Rails transitions - change_column with type conversion

Question

I’ve already figured it out a bit and don’t see a satisfactory answer to my problem.

I have a table with a type row column. I want to do the following migration:

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

When I run this, I get the 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 that I can perform this migration using pure SQL, but it would still be better if I could do this with Rails. I went through the Rails code and doesn't seem to have that capability, but maybe someone knows a way?

I'm not interested in: - pure SQL - deleting a column - creating another column, transforming the data, deleting the original, and then renaming

Answer 1

Since I use Postgres, at the moment I went with the SQL solution. Used query:

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

It only works if you have a field filled with true / false type strings (for example, generating a default collection of radio buttons with a forced Boolean type)

Answer 2

If your rows in the smoking column already have valid boolean values, the following statement will change the column type without data loss:

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

Similarly, you can use this operator to cast columns to an integer:

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

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

Answer 3

Not all databases allow changing the type of a column; the commonly used approach is to add a new column of the required type, transfer any data, delete 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 

Answer 4

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 can add some more WHEN - THEN conditions to your expression

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

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