banditKing banditKing - 7 months ago 101
Ruby Question

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

I created a table in my rails app with rails generate migrations command. Here is that migration file:

class CreateListings < ActiveRecord::Migration
def change
create_table :listings do |t|
t.string :name
t.string :telephone
t.string :latitude
t.string :longitude

t.timestamps
end
end
end


Then I wanted to store the latitude and longitude as integers so
I tried to run:

rails generate migration changeColumnType


and the contents of that file are:

class ChangeColumnType < ActiveRecord::Migration
def up
#change latitude columntype from string to integertype
change_column :listings, :latitude, :integer
change_column :listings, :longitude, :integer
#change longitude columntype from string to integer type
end

def down
end
end


I was expecting the column type to change however the rake was aborted and the following error message appeared. I was wondering why this did not go through? Im using postgresql in my app.

rake db:migrate
== ChangeColumnType: migrating ===============================================
-- change_column(:listings, :latitude, :integer)
rake aborted!
An error has occurred, this and all later migrations canceled:

PG::Error: ERROR: column "latitude" cannot be cast to type integer
: ALTER TABLE "listings" ALTER COLUMN "latitude" TYPE integer

Tasks: TOP => db:migrate
(See full trace by running task with --trace)


NOTE: The table has no DATA.
Thanks

Answer

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 convertible to integer.
If you have defined a DEFAULT for the column, you may have to drop and recreate that for the new type.

Here is blog article on how to do this with ActiveRecord.
Or go with @mu's advice in the comment. He knows his Ruby. I am only good with the PostgreSQL here.