When using Rails, you’re using migrations a lot. Migrations are awesome. But today I ran into a problem. I had to convert a column to be of type :boolean ( it was of type :text ). I was suprised when things went wrong.

When running the migration I got this error from Postgresql:
cannot be cast to type "pg_catalog.bool"

Apparantly, Postgresql can’t convert a text column into a boolean, even when passing default values. The standard way of writing the migration didn’t work:
change_column :projects, :status, :boolean, :default=>true

So I started googling and ended up with:
execute "alter table projects ALTER COLUMN status TYPE boolean USING CASE status WHEN '1' THEN true ELSE false END;"

While this line did work, there is a problem with it; it’s not agnostic. It’s written specifically for Postgresql, but may cause issues in other environments.

So, the final, agnostic solution ( thanks Simon! ) uses plain Rails code. Basically we add a temporary column of type :boolean to store the information. After looping over all Project records we have converted all text values to the type boolean. Because we have all information stored in the temporary column, it’s ok to remove the existing status column and rename convert_status to status.

class ConvertStatusToBoolean < ActiveRecord::Migration
  def self.up
    add_column :projects, :convert_status, :boolean, :default => true

    # look up the schema's to be able to re-inspect the Project model
    # http://apidock.com/rails/ActiveRecord/Base/reset_column_information/class
    Project.reset_column_information

    # loop over the collection
    Project.all.each do |p|
        p.convert_status = p.status == '1'
        p.save
    end

    # remove the older status column
    remove_column :projects, :status
    # rename the convert_status to status column
    rename_column :projects,:convert_status,:status
  end

  def self.down
    change_column :projects, :status, :text
  end
end

Handy cheat sheet:
http://dizzy.co.uk/ruby_on_rails/cheatsheets/rails-migrations#execute

Useful info on Apidock:
http://apidock.com/rails/ActiveRecord/Base/reset_column_information/class