Rails Migrations: tried to change the type of column from string to integer

by

I quote the manual about ALTER TABLE:

A USING clause must be provided if there is no implicit or assignment
cast from old to new type.

What you need is:

ALTER TABLE listings ALTER longitude TYPE integer USING longitude::int;
ALTER TABLE listings ALTER latitude  TYPE integer USING latitude::int;

Or shorter and faster (for big tables) in one command:

ALTER TABLE listings
  ALTER longitude TYPE integer USING longitude::int
, ALTER latitude  TYPE integer USING latitude::int;

This works with or without data as long as all entries are valid as integer.
If the column has a DEFAULT, you may have to drop that (before the above) and recreate (after the above) for the new type.

Here is a blog article on how to do this with ActiveRecord.
Or go with @mu’s advice in the comment. He knows his Ruby. I only know well about the PostgreSQL part.

Leave a Comment