CODEWITHSUNDEEP
postgresqlpostgresql-9.1
David S
David S

Reputation: 13861

Rename enum item in PostgreSQL

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.

Upvotes: 80

Views: 36405

Answers (4)

cstroe
cstroe

Reputation: 4236

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'

Upvotes: 153

Dondi Michael Stroma
Dondi Michael Stroma

Reputation: 4800

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

Upvotes: 44

EM0
EM0

Reputation: 6327

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.

Upvotes: 13

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95592

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.

Upvotes: 7

Related Questions