Rails Change Column Type Migration for Both MySQL and Postgres

Question

I have a rails application which is running on different databases depends on client preferences. I have a "catalog_item_specifications" table which has a column "bec_id". The type of the column is string. Now I want to change the type of the column to integer. When I write:

change_column :catalog_item_specifications, :bec_id, :integer

It works fine on MySQL but failed on postgres. So according to this post: Rails - gmaps4rails gem on postgres I changed my migration to the following:

connection.execute(%q{
    alter table catalog_item_specifications
    alter column bec_id
    type integer using bec_id::integer
})

It now work on Postgres but failed on MySQL. I need a solution (without dropping and re-adding) which will work for both.

smudge · Accepted Answer · 2013-12-11 20:33:04Z

1

This is one of those cases where you might be best just checking which database engine is running, and then use the command that works for that engine.

Something like this:

if connection.adapter_name.downcase == 'postgresql'
  connection.execute(%q{
    alter table catalog_item_specifications
    alter column bec_id
    type integer using bec_id::integer
  })
else
  change_column :catalog_item_specifications, :bec_id, :integer
end

Where 'postgresql' is the adapter_name (you'll have to check to see what it actually is when you are migrating against Postgres).

edited Dec 11, 2013 at 20:33

answered Dec 10, 2013 at 8:41

smudge

8411 gold badge6 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ali Hasan Imam Over a year ago

I actually end up with this solution, than saw same answer here. Good to know that I was on right track!

Collectives™ on Stack Overflow

Rails Change Column Type Migration for Both MySQL and Postgres

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related