How to convert a table column to another data type

Question

I have a column with type Varchar in my Postgres database that I would like to represent integers ... and now I want to change them, unfortunately this does not work using my rail migration.

change_column :table1, :columnB, :integer 

Which seems to output this SQL:

 ALTER TABLE table1 ALTER COLUMN columnB TYPE integer 

So I tried to do this:

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

but this instance does not work because some of the columns have a null value ...

any ideas?

Mistake:

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

Postgres v8.3

Answer 1

It seems like the problem is that your table has empty rows. You will need to handle them, possibly with a case case, for example:

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 an updated question.

Answer 2

NULL should not be a problem here. Tell us your version of postgresql and your error message. Also, why do you quote identifiers? Keep in mind that unquoted identifiers are converted to lower case (the default behavior), so there may be a problem with your "column B" in your query - first it is quoted, not quoted in the listing.

Update. Before you convert a column to an integer, you must be sure that all of your values ​​are convertible. In this case, this means that columnB should only contain numbers (or null). You can check it out for something like

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

If you want your empty strings to be converted to NULL integers, do it first

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