Upgrading enum type column to varchar in postgresql

Question

I have an enum type column in my table . I have now decided to make it a varchar type and put some constraint.

Q1) Which is a good practice : to have enums or to put constarint on the column.

Q2) How to change my enum type column to varchar. Just opposite to this question question . I tried using this:

ALTER TABLE tablename ALTER COLUMN columnname  TYPE VARCHAR

But this gives me the error : No operator matches the given name and argument type(s). You might need to add explicit type casts.

This is the table definition:

CREATE TABLE tablename (
    id1 TEXT NOT NULL,
    id2 VARCHAR(100) NOT NULL,
    enum_field table_enum,
    modified_on TIMESTAMP NOT NULL DEFAULT NOW(),
    modified_by VARCHAR(100),
    PRIMARY key (id1, id2)
); 

Answer 1

For future reference: I had the similar issue with altering enum type and I got the same error message as the one above. In my case, the issue was caused by having a partial index that referenced a column that was using that enum type.

Answer 2

As for best practice, it is best if you define a separate table of possible values, and make your column a foreign key to this table. This has the following benefits:

• The new table can not only have the specific key as a type, it can have additional columns with details such as a friendly name, meaning of the type or more information
• Changing the possible values is a matter of manipulating data rows (INSERT,UPDATE or DELETE) which is more accessible and manageable than changing constraints or enum values.