Rails Migration - PG::Error: ERROR: invalid input

问题:

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

回答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.

回答2:

Another solution to this problem is to create a function. I struggled with this for hours so thought it worth posting the solution. I first create a function to convert all char values in the column to integer

CREATE OR REPLACE FUNCTION char_to_integer(char character varying)
  RETURNS integer AS
$BODY$
SELECT CASE WHEN trim($1) SIMILAR TO '[0-9]+' 
        THEN CAST(trim($1) AS integer) 
    ELSE NULL END;

$BODY$
  LANGUAGE 'sql' IMMUTABLE STRICT;

Now with the USING syntax, we can solve this annoying issue with this command.

ALTER TABLE table_name ALTER COLUMN column_name TYPE integer USING char_to_integer(column_name);