How do you reorder database columns in Rails/PostgreSQL?

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

Migrate

⚠️ 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

Results

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

Caveats

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.

Resources

Stack Overflow
Postgres Wiki