Upgrading a varchar column to enum type in postgresql
Got it.
ALTER TABLE tableName
ALTER COLUMN varcharColumn TYPE enum_type
USING varcharColumn::enum_type
will update it successfully.
You need to define a cast to be used because there is no default cast available.
If all values in the varcharColumn
comply with the enum definition, the following should work:
alter table foo
ALTER COLUMN varcharColumn TYPE enum_type using varcharColumn::enum_type;
I personally don't like enums because they are quite unflexible. I prefer a check constraint on a varchar column if I want to restrict the values in a column. Or - if the list of values changes often and is going to grow - a good old "lookup table" with a foreign key constraint.