I have got a area that's utilizing an enumeration type. If only to update the enum with an additional area (I'd rather not remove anything, just give a new label). What's the easiest method of doing this?

I'd exactly the same problem couple of days ago and located this publish. So my answer could be useful for somebody who's searching for solution :)

For those who have just one or two posts designed to use the enum type you need to change, you can test this. You can also alter the order of values within 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_column::text::some_enum_type;
-- 6. remove old column and type
alter table some_table drop column _some_column;
drop type _some_enum_type;

3-6 ought to be repeated if there's a lot more than 1 column.

Disclaimer: I've not attempted this solution, therefore it could no work -)

You ought to be searching at pg_enum. Should you simply want to alter the label of the existing enum, an easy UPDATE is going to do it.

To include a brand new enum values: Firstly you place the brand new value into pg_enum. When the new value ought to be the last, again you are done. Otherwise (you have to a brand new enum value between existing ones), you will need to update each distinct value inside your table, going in the "biggest" towards the cheapest... After you'll have to relabel them in pg_enum within the opposite order.

Illustration: you've enum ('enum1', 'enum2', 'enum3') and you need to visit ('enum1', 'enum1b', 'enum2', enum3') place 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 title='enum1b' where title='enum2' and enumtypid=OID and so forth...

I can not appear to publish a comment, so I'll just state that upgrading pg_enum works in Postgres 8.4 . For that way our enums are positioned up, I have added new values to existing enum types via:

INSERT INTO pg_enum (enumtypid, enumlabel)
  SELECT typelem, 'NEWENUM' FROM pg_type WHERE

It is a little frightening, but it seems sensible because of the way Postgres really stores its data.

Easiest: eliminate enums. They aren't easily flexible, and therefore should very rarely be utilized.

Upgrading pg_enum works, along with the intermediary column trick outlined above. It's possible to also employ USING miracle to alter the column's type directly:

CREATE TYPE test AS enum('a', 'b');
CREATE TABLE foo (bar test);
INSERT INTO foo VALUES ('a'), ('b');


CREATE TYPE test as enum('a', 'b', 'c');

WHEN bar = ANY (enum_range(null::test)::varchar[])
THEN bar::test
WHEN bar = ANY ('{convert, these, values}'::varchar[])
THEN 'c'::test

As lengthy as is available no functions that clearly require or return that enum, you are good. (pgsql will complain whenever you drop the kind if you will find.)

Also, observe that PG9.1 is presenting an ALTER TYPE statement, that will focus on enums:


PostgreSQL 9.1 introduces capability to ALTER Enum types:

ALTER TYPE enum_type ADD 'new_value' BEFORE 'old_value';
ALTER TYPE enum_type ADD 'new_value' AFTER'old_value';

When utilizing Navicat you can turn to types (under view -> others -> types) - obtain the design look at the kind - and click on the "add label" button.