Rails migrations - change_column with type conversion

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

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.

Not all databases allow changing of column type, the generally taken approach is to add a new column of the desired type, bring any data across, remove the old column and rename the new one.

add_column :users, :smoking_tmp, :boolean

User.reset_column_information # make the new column available to model methods
User.all.each do |user|
  user.smoking_tmp = user.smoking == 1 ? true : false # If smoking was an int, for example
  user.save
end

# OR as an update all call, set a default of false on the new column then update all to true if appropriate.
User.where(:smoking => 1).update_all(:smoking_tmp = true) 

remove_column :users, :smoking
rename_column :users, :smoking_tmp, :smoking

So right for boolean in postgres:

change_column :table_name, :field,'boolean USING (CASE field WHEN \'your any string as true\' THEN \'t\'::boolean ELSE \'f\'::boolean END)'

and you may add some more WHEN - THEN condition in your expression

For other database servers, the expression will be constructed based on the syntax for your database server, but the principle is the same. Only manual conversion algorithm, entirely without SQL there is not enough unfortunately.

The syntax change_column :table, :field, 'boolean USING CAST(field AS boolean)' is suitable only if the contents of the field something like: true / false / null

Since I'm using Postgres, I went with SQL solution for now. Query used:

    execute 'ALTER TABLE "users" ALTER COLUMN "smoking" TYPE boolean USING CASE WHEN "flatshare"=\'true\' THEN \'t\'::boolean ELSE \'f\'::boolean END'

It works only if one has a field filled with true/false strings (such as default radio button collection helper with forced boolean type would generate)