Rails change column type and update column values

Question

I have a table called Users with a column Active that is type boolean. I will soon need to change the type of that column to string. I will also need to change all of the column values of Active from :true to "active" and :false to "inactive".

To change the column type I would use Change a column type from Date to DateTime during ROR migration

class ChangeColumnTypeInUsers < ActiveRecord::Migration
  def up
    change_column :users, :active, :string
  end

  def down
    change_column :users, :active, :boolean
  end
end

How would I update the column value so that the type change doesn't break it? Will it automatically convert :true to "true"?

Answer 1

You can do it in one go quite easily using the USING clause of ALTER TABLE:

The optional USING clause specifies how to compute the new column value from the old; if omitted, the default conversion is the same as an assignment cast from old data type to new.

A simple SQL type cast would leave you with the strings 'true' and 'false' so you do want to add a USING. I'd bypass AR and do it by hand:

connection.execute(%q(
  alter table users
  alter column active
  type text
  using case when active then 'active' else 'inactive' end
))

The important part for you is the using case ... part at the end. You can use that together with the usual AR-ish change_column stuff by tricking AR into doing The Right Thing:

class ChangeColumnTypeInUsers < ActiveRecord::Migration
  def up
    change_column :users, :active, "text using case when active then 'active' else 'inactive' end"
  end

  def down
    change_column :users, :active, "boolean using active = 'active'"
  end
end

Note that I'm using text as the column type. Rails will using varchar(255) inside the database when you say :string without a limit, that's pretty pointless with PostgreSQL as it handles the storage for all the string types pretty much the same internally, the length restrictions on char(n) and varchar(n) actually make them more expensive to use than text. Then only time :string makes sense with PostgreSQL is when you have a reason to include a specific :limit (and then a text column with a CHECK constraint on the length would make more sense but AR is too dumb to know about "advanced" things like CHECK constraints).

Answer 2

An easy way to make your migration flawlessly is to rename your boolean :active column. Add the new column. Run a SQL update and then remove the unused column. All could be done in the same migration. Like this. Down migration not included, so use at your own peril :).

class ChangeColumnTypeInUsers < ActiveRecord::Migration
  def up
    rename_column :users, :active, :active_boolean
    add_column :users, :active, :string
    execute "UPDATE users SET active = 'true' WHERE active_boolean = true"
    execute "UPDATE users SET active = 'inactive' WHERE active_boolean = false"
    remove_column :users, :active_boolean
  end
end