Rails migrations - change_column with type conversion

Question

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

Answer 1

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.

Answer 2

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