Postgres does not currently support the concept of column re-ordering, so there's not an easy way to do this. But if you have to, you can create a new column and copy the original data to it, effectively "moving" a column.
Suppose you have this table in your schema and you need to move
column_b to the end of the table.
ActiveRecord::Schema.define(version: 2019_09_08_204533) do create_table "table_xyz", force: :cascade do |t| t.string "column_a" t.integer "column_b" t.string "column_c" t.integer "column_d" t.string "column_e" end end
Create a new migration
> rails generate migration RearrangeColumns
Fill in the migration steps
There's not an easy way to move the column back to it's original place, so use the
up statement to create an migration that only happens in one direction:
class RearrangeColumns < ActiveRecord::Migration[6.0] def up # Add a temporary column. # Make sure to use the same data type. add_column :table_xyz, :tmp_column_b, :integer # Copy the data. execute <<-SQL UPDATE table_xyz SET tmp_column_b = column_b; SQL # Remove the original column. remove_column :table_xyz, :column_b # Rename the new column. # Same name as the old column. rename_column :table_xyz, :tmp_column_b, :column_b # Rebuild your indexes, if necessary. # add_index :table_xyz, :column_b end end
⚠️ Depending on the size of your table, this might take a while. Make sure to test this before you deploy it to production.
> rails db:migrate
As far as anyone knows, this column has now been moved. 🎉
ActiveRecord::Schema.define(version: 2019_09_08_205407) do create_table "table_xyz", force: :cascade do |t| t.string "column_a" t.string "column_c" t.integer "column_d" t.string "column_e" t.integer "column_b" end end
If you want to move multiple columns, you may need to do this many times. To "move" a column to the "middle" of the table, you'll need to move any column that you want "after" the column you're moving. This quickly gets very tedious.
When is reordering columns useful?
This is probably never really necessary, but if you do a lot of SQL work in the database, it might be useful to have the columns in a different order by default. If you can avoid it, it will probably be easier to query the column order you want or set up a view that displays your desired order.