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 =======…

Accepted 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 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.

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

Solution

Problem