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 book lover

Growing Rails Applications in Practice

Check out our e-book. Learn to structure large Ruby on Rails codebases with the tools you already know and love.

  • Introduce design conventions for controllers and user-facing models
  • Create a system for growth
  • Build applications to last
Read more Show archive.org 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)