PostgreSQL - Updating an Enum Type

Question

I've got a field that is using an enumeration type. I wish to update the enum to have an additional field (I don't want to delete anything, just add a new label). What is the simplest way to do this?

Answer 1

Simplest: get rid of enums. They are not easily modifiable, and thus should very rarely be used.

Answer 2

Disclaimer: I haven't tried this solution, so it might just no work ;-)

You should be looking at pg_enum. If you only want to change the label of an existing enum, a simple UPDATE will do it.

To add a new enum values: First you insert the new value into pg_enum. If the new value should be the last, again you're done. If not (you need to a new enum value in between existing ones), you'll have to update each distinct value in your table, going from the "largest" to the lowest... After you'll just have to rename them in pg_enum in the opposite order.

Illustration: you have enum ('enum1', 'enum2', 'enum3') and you want to go to ('enum1', 'enum1b', 'enum2', enum3') insert into pg_enum (OID, 'newenum3'); update table set enumvalue to 'newenum3' where enumvalue='enum3'; update table set enumvalue to 'enum3' where enumvalue='enum2'; then update table pg_enum set name='enum1b' where name='enum2' and enumtypid=OID; and so on...

Answer 3

I had the same problem few days ago and found this post. So my answer can be helpful for someone who is looking for solution :)

If you have only one or two columns which use the enum type you want to change, you can try this. Also you can change the order of values in the new type.

-- 1. rename the enum type you want to change
alter type some_enum_type rename to _some_enum_type;
-- 2. create new type
create type some_enum_type as enum ('old', 'values', 'and', 'new', 'ones');
-- 3. rename column(s) which uses our enum type
alter table some_table rename column some_column to _some_column;
-- 4. add new column of new type
alter table some_table add some_column some_enum_type not null default 'new';
-- 5. copy values to the new column
update some_table set some_column = _some_enum_type::text::some_enum_type;
-- 6. remove old column and type
alter table some_table drop column _some_enum_type;
drop type _some_enum_type;

3-6 should be repeated if there is more than 1 column.

Answer 4

I can't seem to post a comment, so I'll just say that updating pg_enum works in Postgres 8.4 . For the way our enums are set up, I've added new values to existing enum types via:

INSERT INTO pg_enum (enumtypid, enumlabel)
  SELECT typelem, 'NEWENUM' FROM pg_type WHERE
    typname = '_ENUMNAME_WITH_LEADING_UNDERSCORE';

It's a little scary, but it makes sense given the way Postgres actually stores its data.