Rename enum item in PostgreSQL

Question

I would like to change the name of an item in an enum type in PostgreSQL 9.1.5.

Here is the type's create stmt:

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

I just want to change 'Task created' to 'Aborted'. It seems like from the documentation, that the following should work:

ALTER TYPE import_action RENAME ATTRIBUTE "Task created" TO "Aborted";  

However, I get a msg:

********** Error **********  ERROR: relation "import_action" does not exist SQL state: 42P01 

But, it clearly does exist.

The type is currently being used by more than one table.

I'm being to think that there must not be a way to do this. I've tried the dialog for the type in pgAdminIII, but there is no way that I can see to rename the it there. (So, either a strong hint that I can't do it, or - I'm hoping - a small oversight be the developer that created that dialog)

If I can't do this in one statment? Then what do I need to do? Will I have to write a script to add the item, update all of the records to new value, then drop the old item? Will that even work?

It's seems like this should be a simple thing. As I understand it, the records are just storing a reference to the type and item. I don't think they are actually store the text value that I have given it. But, maybe I'm wrong here as well.

Answer 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' 

Answer 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' )