Read more

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

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 web development

Do you need DevOps-experts?

Your development team has a full backlog? No time for infrastructure architecture? Our DevOps team is ready to support you!

  • We build reliable cloud solutions with Infrastructure as code
  • We are experts in security, Linux and databases
  • We support your dev team to perform
Read more Show snapshot

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)