heroku: PG::InvalidTextRepresentation: ERROR: invalid input syntax for integer: “”

Question

I have a currency(ccy) column on my event model and it's currently a string, I'd like to change it to a integer.

Although it works on my local environment, the following error was displayed when I try heroku run rake db:migrate to heroku.

rake aborted!
StandardError: An error has occurred, this and all later migrations canceled:

PG::InvalidTextRepresentation: ERROR:  invalid input syntax for integer: ""
: ALTER TABLE "events" ALTER COLUMN "ccy" TYPE integer USING CAST(ccy AS integer)

The migration file as below.

class ChangeCcyToEvents < ActiveRecord::Migration
  def change
    change_column :events, :ccy, 'integer USING CAST(ccy AS integer)'
  end
end

I found the similar question PG::InvalidTextRepresentation: ERROR: invalid input syntax for integer: "M", so I changed migration file as below. But the result is the same.

class ChangeCcyToEvents < ActiveRecord::Migration
  def change
    Event.where(ccy: '').update_all(ccy: '')
    change_column :events, :ccy, 'integer USING CAST(ccy AS integer)'
  end
end

There is no value in all ccy column so far.

It would be appreciated if you could give me any advice.

Community · Accepted Answer · 2017-05-23 12:15:59Z

4

Your migration is trying to convert a "" into an integer, which postgres complais about it (and it should, because it's not a valid conversion).

You should update your content for invalid cases before changing the column type:

class ChangeCcyToEvents < ActiveRecord::Migration
  def up
    Event.where("ccy IS NULL OR ccy = ''").update_all({ccy: '0'})
    change_column :events, :ccy, 'integer USING CAST(ccy AS integer)'
  end

  def down
    raise ActiveRecord::IrreversibleMigration
  end
end

Perhaps is not complaining in development (localhost) because you're using a different version of postgreSQL than Heroku.

Next time, try using default values in migrations for this kind of columns.

edited May 23, 2017 at 12:15

CommunityBot

11 silver badge

answered Jun 11, 2016 at 19:30

Wikiti

1,63613 silver badges21 bronze badges

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

6 Comments

Roman Kiselenko Over a year ago

You can't use return like that ruby != javascript , it should raise an error LocalJumpError: unexpected return. I think you mean next

Wikiti Over a year ago

@Зелёный You're right! I have changed it to a next command.

Roman Kiselenko Over a year ago

Also it is work for update_all method.

Roman Kiselenko Over a year ago

And last, the change_columnmethod is irreversible.

SamuraiBlue Over a year ago

Thank you for your answer and comment, @Wikiti and @Зелёный. Although I tried the migration file which @Wikiti answered, the following error was appeared.

StandardError: An error has occurred, this and all later migrations canceled:  uninitialized constant ChangeCcyToEvents::Events

At the middle of error NameError: uninitialized constant ChangeCcyToEvents::Events was displayed. It would be appreciated if you could give me any advice.

|

Collectives™ on Stack Overflow

heroku: PG::InvalidTextRepresentation: ERROR: invalid input syntax for integer: “”

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related