Postgresql Datatype mismatch default for column cannot be cast to type integer

When converting a table column from varchar to integer I ran into a problem.

[code lang=text]
SQLSTATE[42804]: Datatype mismatch: 7 ERROR: default for column "column_name" cannot be cast to type integer
[/code]

The solution I could find on (Stackoverflow)[http://stackoverflow.com/questions/13170570/change-type-of-varchar-field-to-integer-cannot-be-cast-automatically-to-type-i] was not working:

[code lang=sql]
ALTER TABLE the_table ALTER COLUMN col_name TYPE integer USING (col_name::integer);
[/code]

Looking closer at the error I realised – as is often the case – the error was indicating exactly what the problem was. The default of the column could not be converted.

Not knowing how to convert the default, I simply changed it to something that could be converted:

[code lang=sql]
ALTER TABLE the_table ALTER COLUMN col_name SET DEFAULT 0;
[/code]

Despite it being a varchar column, the integer value 0 was accepted. After which the earlier CAST worked.

Leave a Reply

Your email address will not be published. Required fields are marked *