Rename enum item in PostgreSQL

Solution 1:

In PostgreSQL version 10, the ability to rename the labels of an enum has been added as part of the ALTER TYPE syntax:

ALTER TYPE name RENAME VALUE 'existing_enum_value' TO 'new_enum_value'

Solution 2:

Update: For PostgreSQL version 10 or later, see the top-voted answer.

Names of enum values are called labels, attributes are something different entirely.

Unfortunately changing enum labels is not simple, you have to muck with the system catalog: http://www.postgresql.org/docs/9.1/static/catalog-pg-enum.html

UPDATE pg_enum SET enumlabel = 'Aborted' 
WHERE enumlabel = 'Task created' AND enumtypid = (
  SELECT oid FROM pg_type WHERE typname = 'import_action'
)

Solution 3:

The query in the accepted answer doesn't take into account schema names. Here's a safer (and simpler) one, based on http://tech.valgog.com/2010/08/alter-enum-in-postgresql.html

UPDATE pg_catalog.pg_enum
SET enumlabel = 'NEW_LABEL'
WHERE enumtypid = 'SCHEMA_NAME.ENUM_NAME'::regtype::oid AND enumlabel = 'OLD_LABEL'
RETURNING enumlabel;

Note that this requires the "rolcatupdate" (Update catalog directly) permission - even being a superuser is not enough.

It seems that updating the catalog directly is still the only way as of PostgreSQL 9.3.

Solution 4:

There's a difference between types, attributes, and values. You can create an enum like this.

CREATE TYPE import_action AS ENUM
('Ignored',
'Inserted',
'Updated',
'Task created');

Having done that, you can add values to the enum.

ALTER TYPE import_action 
ADD VALUE 'Aborted';

But the syntax diagram doesn't show any support for dropping or renaming a value. The syntax you were looking at was the syntax for renaming an attribute, not a value.

Although this design is perhaps surprising, it's also deliberate. From the pgsql-hackers mailing list.

If you need to modify the values used or want to know what the integer is, use a lookup table instead. Enums are the wrong abstraction for you.