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.

Answer 1

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