Rails migration : Change column type and update existing data

Question

In my Rails project, I have a model object which has an existing column called 'permanent' of type 'Boolean'. I need to change this column to an Integer with possible values of 1, 2, 3. Is there any way for me to update the existing data in the DB (through the rails migration) such that all rows with 'permanent' as false are changed to 1 and all rows with 'permanent' as true are changed to 2.

Which SQL db you are using?

ray
– ray

2019-02-21 05:27:25 +00:00
Commented Feb 21, 2019 at 5:27 — ray
– ray, Commented Feb 21, 2019 at 5:27

Eldar · Accepted Answer · 2019-02-21 06:13:58Z

2

I am using Postgres. Not sure whether this solution works for other databases. people table used as an example - do not forget to change the table name to your own.

  def up
    change_column :people, :permanent, 'integer USING CAST(permanent AS integer)'
    Person.connection.execute("UPDATE people SET permanent = CASE permanent WHEN 0 THEN 1 WHEN 1 THEN 2 END")
  end

  def down
    Person.connection.execute("UPDATE people SET permanent = CASE permanent WHEN 1 THEN 0 WHEN 2 THEN 1 END")
    change_column :people, :permanent, 'boolean USING CAST(permanent AS boolean)'
  end

edited Feb 21, 2019 at 6:13

answered Feb 21, 2019 at 5:37

Eldar

1181 silver badge6 bronze badges

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

5 Comments

ray Over a year ago

Above can be done by rake task. It is always better to use SQL queries to do such operations.

Eldar Over a year ago

@ray thanks for your advice! Why is it better to use SQL queries for such operations?

ray Over a year ago

Actual motive to write migration is to use ActiveRecord::Migration methods, here execute method will be better one to run SQL to update existing data. Otherwise I could run migration in controller also like ActiveRecord::Migration.create_table.. if condition

ray Over a year ago

Perhaps your answer do not harm and will work properly as if you have provided proper down method & handled well. But it do not satisfy coding convention.

Eldar Over a year ago

@ray - I updated the solution according to your comments, does it satisfy to coding conventions now?

Collectives™ on Stack Overflow

Rails migration : Change column type and update existing data

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related