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.

score 4 · Accepted Answer · edited May 23 '17 at 12:15

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 '17 at 12:15

Community

1
1

answered Jun 11 '16 at 19:30

Wikiti

1,626
13
21

1

You can't use `return` like that `ruby != javascript` , it should raise an error `LocalJumpError: unexpected return`. I think you mean `next` – Roman Kiselenko Jun 11 '16 at 19:38
@Зелёный You're right! I have changed it to a `next` command. – Wikiti Jun 11 '16 at 19:39
1

Also it is work for [`update_all`](http://apidock.com/rails/v4.0.2/ActiveRecord/Relation/update_all) method. – Roman Kiselenko Jun 11 '16 at 19:40
1

And last, the [`change_column`](http://edgeguides.rubyonrails.org/active_record_migrations.html#changing-columns)method is *irreversible*. – Roman Kiselenko Jun 11 '16 at 19:44
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. – SamuraiBlue Jun 11 '16 at 20:44
@SamuraiBlue My fault! It's not `Events`, but `Event`. Model names are singular, and table names are plural – Wikiti Jun 11 '16 at 21:07

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

1 Answers1

Linked