Postgresql – In Postgres, how to cast character varying to an enum type

castpostgresql

I have a Postgres 8.4 table with columns that are of type "character varying", but I want to convert them to an enum type.

When trying to alter the table

ALTER TABLE logs ALTER COLUMN interface_type TYPE interface_types
USING interface_type::text::interface_types;

I get an error

default for column "interface_type" cannot be cast to type interface_types

The default value for the column IS a text version of a value in the enum types. So it should be possible, though I've had no luck googling for the solution.

I assume that I need to define a cast for it. How?

Best Answer

I think I've found the answer: I need to drop the default first, and then re-add it.

ALTER TABLE logs ALTER COLUMN interface_type DROP DEFAULT;
ALTER TABLE logs ALTER COLUMN interface_type TYPE interface_types
  USING interface_type::text::interface_types;
ALTER TABLE logs ALTER COLUMN interface_type SET DEFAULT 'button';
Related Question