Rails Migration Error w/ Postgres when pushing to Heroku

Question

I'm trying to perform the following up migration to change the column "number" in the "tweet" model's table

class ChangeDataTypeForTweetsNumber < ActiveRecord::Migration
  def up
    change_column :tweets do |t|
      t.change :number, :integer
    end
  end

  def down
    change_table :tweets do |t|
      t.change :number, :string
    end
  end
end

Upon performing the the following up migration to heroku....

heroku rake db:migrate:up VERSION=20120925211232

I get the following error

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

Any thoughts you have would be very much appreciated.

Thanks everyone.

riley · Accepted Answer · 2015-07-29 19:40:45Z

47

Same as above but a little bit more concise:

change_column :yourtable, :column_to_change, 'integer USING CAST("column_to_change" AS integer)'

edited Jul 29, 2015 at 19:40

answered Oct 16, 2013 at 7:51

riley

2,4671 gold badge27 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Rob Over a year ago

Feels like AR should handle this really

febeling Over a year ago

Use this for a reversible migration in that case:

reversible do |dir|       dir.up do         change_column :yourtable, :column_to_change, 'integer USING CAST("column_to_change" AS integer)'       end       dir.down do         change_column :yourtable, :column_to_change, 'character varying USING CAST("column_to_change" AS character varying)'       end     end

mu is too short · Accepted Answer · 2012-09-26 16:05:56Z

32

From the fine manual:

[ALTER TABLE ... ALTER COLUMN ...]
The optional USING clause specifies how to compute the new column value from the old; if omitted, the default conversion is the same as an assignment cast from old data type to new. A USING clause must be provided if there is no implicit or assignment cast from old to new type.

There is no implicit conversion from varchar to int in PostgreSQL so it complains that column "number" cannot be cast to type integer and the ALTER TABLE fails. You need to tell PostgreSQL how to convert the old strings to numbers to match the new column type and that means that you need to get a USING clause into your ALTER TABLE. I don't know of any way to make Rails do that for you but you can do it by hand easily enough:

def up
  connection.execute(%q{
    alter table tweets
    alter column number
    type integer using cast(number as integer)
  })
end

You'll want to watch out for values that can't be cast to integers, PostgreSQL will let you know if there are problems and you'll have to fix them before the migration will succeed.

Your existing down-migration should be fine, converting integer to varchar should be handled automatically.

answered Sep 26, 2012 at 16:05

mu is too short

436k71 gold badges863 silver badges822 bronze badges

5 Comments

Dan Mazzini Over a year ago

For a more concise and idiomatic way to do it, check out riley's answer below!

mu is too short Over a year ago

@danmaz74: Do you know if that was available in 2012 or did I miss something?

Dan Mazzini Over a year ago

@muistooshort I didn't want to criticize your answer, just highlight the other one (which came after yours) - many people don't read "other answers", but read comments to the top one

mu is too short Over a year ago

@danmaz74: No worries, I'm not throwing a hissy fit or anything like that, just curious if you knew if the "SQL snippet" argument to change_column is something new and official or just a trick that works because of the loose way that change_column is implemented. The Rails docs suggest that it is a trick but the Rails docs leave out all sorts of important things.

Dan Mazzini Over a year ago

@muistooshort I honestly have no idea :)

Collectives™ on Stack Overflow

Rails Migration Error w/ Postgres when pushing to Heroku

2 Answers 2

2 Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related