how to change the position of a new column in rails 3.x migration

Question

I created a migration that simply adds a new column, but I want it to be added before the created_at and updated_at columns, is there a way to specify in which position the new column is created ?

I searched and only found someone saying to use the :after option in add_column, but it doesn't do anything. then I looked in the rails api docs and found no such option.

Why does it matter? I don't know anything about an :after option but if you are still in development and you don't have a problem with losing the data stored in your tables, you could always drop that table, and create it from scratch with all the attributes, without needing a migration to add certain columns. — Ashitaka, Jan 28 '12 at 16:07
Just out of curiosity: does that have any impact on smth other than visual readability of the table? — KL-7, Jan 28 '12 at 17:45
I want it for better visual readability of the table, but while searching for a solution I found some blog post about performance effects — Gal Ben-Haim, Jan 28 '12 at 17:51
here you go - http://explainextended.com/2009/05/21/choosing-column-order/ — Gal Ben-Haim, Jan 29 '12 at 20:14

score 5 · Accepted Answer · edited May 23 '17 at 11:53

5

After option works well for me

add_column :table_name, :column_name, :type, :after => :column_name

Also look at this question.

edited May 23 '17 at 11:53

Community

1
1

answered Jan 28 '12 at 17:03

Mikhail Nikalyukin

11,867
1
46
70

I tried the :after option and both in db/schema.rb and sqlitebrowser I see no difference. (ofcourse after resetting the db and running migrations) my development environment is rails 3.2.1 and sqlite – Gal Ben-Haim Jan 28 '12 at 17:56
Seems so, are you using Postgres? Try raw SQL in migration – Mikhail Nikalyukin Jan 28 '12 at 21:37
i'm using sqlite for development, production will probably be postgres on heroku – Gal Ben-Haim Jan 28 '12 at 23:43
2

Just to confirm, yes, the :after option only works against a MySQL DB. For some reason the Postgres adapter was never updated to support this (as of Rails 3.2.13) – James Lai Aug 24 '13 at 15:55
Rails 4 still doesn't support :after for Postgres. – Oscar Barrett Nov 29 '13 at 01:19
2

This is not Rail's omission, PG doesn't allow this: http://dba.stackexchange.com/a/3277 – Lee Apr 04 '14 at 08:42

score 2 · Answer 2 · answered Dec 18 '15 at 16:09

From my research, the :after option does not work for SQLite or Postgres, as these DBMSs do not allow such operation. However, the option works for MySQL.

References:

SQLite: https://www.sqlite.org/lang_altertable.html - this basically tells you to create a new table with correct column ordering, then copy data over.
Postgres: https://wiki.postgresql.org/wiki/Alter_column_position

how to change the position of a new column in rails 3.x migration

2 Answers2

Linked