PostgreSQL vs. Rails migration: How to change columns from string to integer
When writing Rails migrations to convert a
string column to an
integer you'd usually say:
change_column :table_name, :column_name, :integer
However, PostgreSQL will complain:
PG::DatatypeMismatch: ERROR: column "column_name" cannot be cast automatically to type integer HINT: Specify a USING expression to perform the conversion.
The "hint" basically tells you that you need to confirm you want this to happen, and how data shall be converted. Just say this in your migration:
change_column :table_name, :column_name, 'integer USING CAST(column_name AS integer)'
The above will mimic what you know from other database adapters. If you have non-numeric data, results may be unexpected (but you're converting to an integer, after all).