CODEWITHSUNDEEP
ruby-on-railsrubypostgresqlcastingrails-migrations
banditKing
banditKing

Reputation: 9579

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

Upvotes: 32

Views: 30192

Answers (6)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656381

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.

Upvotes: 31

gilcierweb
gilcierweb

Reputation: 2714

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

Upvotes: 0

Joseph N.
Joseph N.

Reputation: 2457

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.

Upvotes: 12

rizidoro
rizidoro

Reputation: 13418

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

Upvotes: 24

neverbendeasy
neverbendeasy

Reputation: 988

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

Upvotes: 27

Victor
Victor

Reputation: 13368

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

Upvotes: 2

Related Questions