Read more

PostgreSQL vs. Rails migration: How to change columns from string to integer

Avatar
Arne Hartherz
October 02, 2013Software engineer at makandra GmbH

When writing Rails migrations to convert a string column to an integer you'd usually say:

change_column :table_name, :column_name, :integer
Illustration online protection

Rails Long Term Support

Rails LTS provides security patches for old versions of Ruby on Rails (2.3, 3.2, 4.2 and 5.2)

  • Prevents you from data breaches and liability risks
  • Upgrade at your own pace
  • Works with modern Rubies
Read more

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).

Posted by Arne Hartherz to makandra dev (2013-10-02 14:32)