How to convert a table column to another data type

问题:

I have a column with the type of Varchar in my Postgres database which I meant to be integers... and now I want to change them, unfortunately this doesn't seem to work using my rails migration.

change_column :table1, :columnB, :integer

Which seems to output this SQL:

ALTER TABLE table1 ALTER COLUMN columnB TYPE integer

So I tried doing this:

execute 'ALTER TABLE table1 ALTER COLUMN columnB TYPE integer USING CAST(columnB AS INTEGER)'

but cast doesn't work in this instance because some of the column are null...

any ideas?

Error:

PGError: ERROR:  invalid input syntax for integer: ""
: ALTER TABLE table1 ALTER COLUMN columnB TYPE integer USING CAST(columnB AS INTEGER)

Postgres v8.3

回答1:

It sounds like the problem is that you have empty strings in your table. You'll need to handle those, probably with a case statement, such as:

execute %{ALTER TABLE "table1" ALTER COLUMN columnB TYPE integer USING CAST(CASE columnB WHEN '' THEN NULL ELSE columnB END AS INTEGER)}

Update: completely rewritten based on updated question.

回答2:

NULLs shouldnt be a problem here. Tell us your postgresql version and your error message. Besides, why are you quoting identifiers ? Be aware that unquoted identifiers are converted to lowercase (default behaviour), so there might be a problem with your "columnB" in your query - it appears quoted first, unquoted in the cast.

Update: Before converting a column to integer, you must be sure that all you values are convertible. In this case, it means that columnB should contains only digits (or null). You can check this by something like

  select columnB from table where not columnB ~ E'^[0-9]+$';

If you want your empty strings to be converted to NULL integers, then run first

  UPDATE table set  columnB = NULL WHERE columnB = '';