CODEWITHSUNDEEP
sqldatabasepostgresqlddl
Gopal
Gopal

Reputation: 1452

Upgrading a varchar column to enum type in postgresql

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

  1. Create another new column with enum type.
  2. Update the enum type column with the varchar column after typecasting.
  3. Drop the varchar column.
  4. Rename the enum type column name to the varchar column name.

Is there a better way to achieve this?

Thanks in advance.

Upvotes: 38

Views: 33706

Answers (3)

droid192
droid192

Reputation: 2232

you as well might consider domain type if you dont need the enum ordering property and are offput by missing delete enum value.
Domain types are user-denfiend types + constraints, which makes x out of a selection possible via CHECK & co and as such not so 'unwieldy like enums.

Typical enum is eg log levels, while domain eg email text input validation with the field being present in many tables.

Upvotes: 0

user330315
user330315

Reputation:

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.

Upvotes: 68

Gopal
Gopal

Reputation: 1452

Got it.

ALTER TABLE tableName
   ALTER COLUMN varcharColumn TYPE enum_type
    USING varcharColumn::enum_type

will update it successfully.

Upvotes: 8

Related Questions