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:
Useful info on Apidock: