Add Column After Column in Migrations


#1

Adding a column to a table just places the column at the end, but I
like to keep related columns together. Is there a way to specify
where the column is added in a table with migrations? For example, in
MySQL you can do:

ALTER TABLE my_table ADD COLUMN new_column INT AFTER other_column

I was hoping migrations would support something like this:

add_column :my_table, :new_column, :integer, :after => :other_column

I couldn’t find anything mentioned in the documentation or in a quick
search of this mailing list.

Thanks,

Ryan


#2

Probably not, because the MySQL syntax is probably not standard SQL.

It’s not directly possible in Oracle, PostGres, SQL Server, DB2, etc.,
but if you need to, it is possible with a couple of different
functions that select the fields you want into a new table with some
default datatype for the field you’re adding (something like this in
SQL Server):

select field_a, field_b, null as new_field, field_c, field_d
into #mytable_temp
from mytable;

truncate table mytable;

drop table mytable;

select *
into mytable
from #mytable_temp;

drop table #mytable_temp;

alter table mytable alter new_field varchar(200);

Or, use a view on the table instead, with the fields ordered how you
like them ordered in the view. With most of the RDBMSs, this should
work just fine.