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.