When converting a table column from
integer I ran into a problem.
SQLSTATE: Datatype mismatch: 7 ERROR: default for column "column_name" cannot be cast to type integer
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:
ALTER TABLE the_table ALTER COLUMN col_name TYPE integer USING (col_name::integer);
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:
ALTER TABLE the_table ALTER COLUMN col_name SET DEFAULT 0;
Despite it being a
varchar column, the integer value 0 was accepted. After which the earlier CAST worked.