We have a varchar column in a table, that we need to upgrade to enum type.
All the values in the varchar column are valid values in the enumeration. There is no null values in the varchar column.
ALTER TABLE tableName
ALTER COLUMN varcharColumn TYPE enum_type
ERROR: column "varcharColumn" cannot be cast to type enum_type SQL state: 42804
The round about way is to
Is there a better way to achieve this?
Thanks in advance.
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.