Rails Migration - PG::Error: ERROR: invalid input syntax for integer: ""

Question

I am trying to deploy my code to heroku and i get the error

-- execute("ALTER TABLE lodges ALTER COLUMN image TYPE integer USING (image::integer)")
PG::Error: ERROR:  invalid input syntax for integer: ""
: ALTER TABLE lodges ALTER COLUMN image TYPE integer USING (image::integer)
rake aborted!

and my migration is

class ChangeDataTypeForLodgesImage < ActiveRecord::Migration
  def change
    execute "ALTER TABLE lodges ALTER COLUMN image TYPE integer USING (image::integer)"
  end
end

Answer 1

That error is telling you that you have empty strings in the lodges.image column and you cannot cast an empty string to an integer. You'll have to fix the broken data before changing the column type. The fix depends on what you want empty strings to be; one possibility would be to convert them to NULLs:

execute %q{
  update lodges
  set image = null
  where image = ''
}
execute "ALTER TABLE lodges ALTER COLUMN image TYPE integer USING (image::integer)"

Or perhaps you want empty strings to be zeros:

execute %q{
  update lodges
  set image = '0'
  where image = ''
}
execute "ALTER TABLE lodges ALTER COLUMN image TYPE integer USING (image::integer)"

There may be other values that you can't cast to integers, you'll have to clean them up similarly.