I recently had trouble manipulating an ENUM field I had created in PostgreSQL. I couldn’t find any suggestions or samples easily on Google or in the manual and was able to get it to work, so I post it here. The basic premise is there is an ENUM field type created, I need more possible values and to preserve the existing values I already have to keep code working.
Initial creation of the type and table:
CREATE TYPE var_type AS ENUM('text', 'number', 'date', 'boolean');
CREATE TABLE custom_fields (
id bigserial PRIMARY KEY,
name varchar(50) NOT NULL,
pdf_type var_type NOT NULL
);
Running with this table for some time, invariably, new rows are created and there’s now a migration consideration. As long as you are not using the table column as a reference in a foreign key, the following should work to preserve the data, drop and re-create the type.
The following creates a new column to hold the original text value:
ALTER TABLE custom_fields ADD COLUMN type_text varchar(15);
UPDATE custom_fields SET type_text = pdf_type::text;
We, then, need to drop the existing type and re-create it with the new values we want. CASCADE automatically drops columns that depend on the type:
DROP TYPE var_type CASCADE;
CREATE TYPE var_type AS ENUM('text', 'number', 'date', 'boolean', 'list');
This last part was what I couldn’t figure out without thinking a little more. When you add it back, you have to cast the varchar column back into the ENUM type. I had tried a variety of concoctions here before getting this to work:
ALTER TABLE custom_fields ADD COLUMN pdf_type var_type;
UPDATE custom_fields SET pdf_type = type_text::var_type;
ALTER TABLE custom_fields ALTER pdf_type SET NOT NULL;
ALTER TABLE custom_fields DROP COLUMN type_text;