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

Question

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 1

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.

Answer 2

I would include the raw SQL in your migration file like below so that it updates schema.rb.

class ChangeColumnType < ActiveRecord::Migration   def up     execute 'ALTER TABLE listings ALTER COLUMN latitude TYPE integer USING (latitude::integer)'     execute 'ALTER TABLE listings ALTER COLUMN longitude TYPE integer USING (longitude::integer)'   end    def down     execute 'ALTER TABLE listings ALTER COLUMN latitude TYPE text USING (latitude::text)'     execute 'ALTER TABLE listings ALTER COLUMN longitude TYPE text USING (longitude::text)'   end end