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

Ensure you have no data on it and you could try make a rollback — Ismael Abreu, Apr 27 '12 at 01:03
If there's no data you can simply remove the columns and re-add them with the correct type. A whole degree of lat/long is pretty big so you might want to think about what type you really want for those columns. — mu is too short, Apr 27 '12 at 01:36

Erwin Brandstetter · Accepted Answer · 2022-11-01T19:06:21.937

31

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.

edited Nov 01 '22 at 19:06

answered Apr 27 '12 at 01:03

Erwin Brandstetter

605,456
145
1,078
1,228

4

For a one-off, the easiest thing to do is wrap the SQL in a `connection.execute('...')` rather that mucking around with monkey patching. – mu is too short Apr 27 '12 at 01:36
1

For solution in rails migration please take a look here: http://stackoverflow.com/questions/10690289/rails-gmaps4rails-gem-on-postgres – cintrzyk Sep 23 '13 at 14:02

neverbendeasy · Answer 2 · 2013-09-25T19:42:00.337

25

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

edited Sep 25 '13 at 19:42

answered Sep 25 '13 at 19:20

neverbendeasy

968
10
17

Note that the down ALTER should be varchar instead of text to match the string type. – Martin Jun 23 '14 at 20:04

score 24 · Answer 3 · answered May 09 '14 at 17:40

24

I know this a bit ugly, but I prefer to just remove the column and add again with the new type:

 def change
     remove_column :mytable, :mycolumn
     add_column :mytable, :mycolumn, :integer, default: 0
 end

answered May 09 '14 at 17:40

rizidoro

13,073
18
59
86

I ran a migration change_column :mytable, :mycolumn, :float. Then ran a couple more migrations, pushed to github, pushed to heroku only to get this error. I ran a new migration as you suggest above, but the error still persists because of the previous migration. Now I can't rollback because remove_column is irreversible. Am I fubar? – tomb Feb 06 '18 at 19:07
Update to prior comment: I deleted the prior migration with 'rails destroy migration migration_name' then rake db:migrate and now all is well. – tomb Feb 06 '18 at 19:46
after hours of trying this helped me to convert boolean to enum – Jose Kj Dec 11 '18 at 22:25

Joseph N. · Answer 4 · 2016-08-23T18:11:02.253

12

The following is a more rails way to approach the problem. For my case I had two columns in my purchases table that I needed to convert from type string to float.

def change
    change_column :purchases, :mc_gross, 'float USING CAST(mc_gross AS float)'
    change_column :purchases, :mc_fee, 'float USING CAST(mc_fee AS float)'
end

That did the trick for me.

edited Aug 23 '16 at 18:11

answered Apr 27 '16 at 19:37

Joseph N.

2,437
1
25
31

While it is good to know how to intervene in postgreSQL, one can lose track of such a change. This rails way leaves a trace for the operation. – Jerome Mar 20 '20 at 16:48

score 2 · Answer 5 · answered Apr 27 '12 at 03:34

2

Do you have existing data in those columns?
You should not use int for latitude and longitude. They should be in floating points instead.

answered Apr 27 '12 at 03:34

Victor

13,010
18
83
146

Using an integer or long in (eg) degrees*10^12 of lat/long can be a lot faster and more storage efficient if you know in advance the resolution you need. PITA to work with, though, and prone to conversion error unless all your algorithms can work with it natively in that form. I agree that double or float is safer unless you know you need something different and have good reasons. – Craig Ringer Apr 27 '12 at 04:20
Here's Google Maps' explanation and recommendation: https://developers.google.com/maps/articles/phpsqlajax#createtable – Victor Apr 27 '12 at 11:34

score 0 · Answer 6 · answered Apr 29 '17 at 15:26

latitude and longitude is decimal

rails g scaffold client name:string email:string 'latitude:decimal{12,3}' 'longitude:decimal{12,3}' 

class CreateClients < ActiveRecord::Migration[5.0]
  def change
    create_table :clients do |t|
      t.string :name
      t.string :email
      t.decimal :latitude, precision: 12, scale: 3
      t.decimal :longitude, precision: 12, scale: 3

      t.timestamps
    end
  end
end

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

6 Answers6

Linked

Related