ActiveRecord column cannot be cast automatically to type numeric

Question

I need to change the data type of a column in a DB from :string to :decimal. All values are in the following format: "129.99", so converting them should not raise any errors.

To do so I wrote the following migration:

def change
  change_column :my_table, :target_column, :decimal
end

When I execute this it shows me the following error together with a hint on how to fix it:

PG::DatatypeMismatch: ERROR:  column "target_column" cannot be cast automatically to type numeric
HINT:  You might need to specify "USING target_column::numeric".

However, I can't seem to find any documentation about how to do this, so the hint doesn't really help me.

Whats the best way to perform this migration?

Answer 1

this should do:

def change
  change_column :my_table, :target_column, 'numeric USING CAST(target_column AS numeric)'
end

Answer 2

For people just wishing to change the column type and that don't care what happens to the data in that field.

Simply delete the column, and re-create it with the correct type:

def change
    remove_column :table_name, :field
    add_column :table_name, :field, :type
end