Rails Migrations – How to Change Column Type with Conversion

ruby-on-railsruby-on-rails-3

I already google'd aroung a little bit and seems there's no satisfying answer for my problem.

I have a table with column of type string.
I'd like to run following migration:

class ChangeColumnToBoolean < ActiveRecord::Migration
    def up
        change_column :users, :smoking, :boolean
    end
end

When I run this I get following error

PG::Error: ERROR:  column "smoking" cannot be cast automatically to type boolean
HINT:  Specify a USING expression to perform the conversion.
: ALTER TABLE "users" ALTER COLUMN "smoking" TYPE boolean

I know I can perform this migration using pure SQL but still it would be nicer if I could do it with Rails. I went through Rails code and seems theres no such possibility, but maybe someone knows a way?

I'm not interested in:
– pure SQL
– dropping the column
– creating another column, converting data, dropping original and then renaming

Best Answer

If your strings in smoking column are already valid boolean values, the following statement will change the column type without losing data:

change_column :users, :smoking, 'boolean USING CAST(smoking AS boolean)'

Similarly, you can use this statement to cast columns to integer:

change_column :table_name, :column_name, 'integer USING CAST(column_name AS integer)'

I am using Postgres. Not sure whether this solution works for other databases.

Related Question