Waiting for 9.1 – adding values to enums

Allow new values to be added to an existing enum type.
After much expenditure of effort, we've got this to the point where the
performance penalty is pretty minimal in typical cases.
Andrew Dunstan, reviewed by Brendan Jurd, Dean Rasheed, and Tom Lane

As you perhaps know ENUM datatype was added to PostgreSQL in 8.3, but (at the very least for me) it was virtually unusable, as it couldn't be modified.

Well, truth be told – it could be modified by changing catalogs, but I just don't feel smart enough to modify catalogs with normal queries, so I just don't do it.

Now – we got a way to add new values to ENUMS.

Let's see how it works. First I will need some enum:

CREATETYPE mood AS ENUM ('sad','ok','happy');

And now I can use the values with casts to enum:

SELECT'sad'::mood;
mood
------
sad
(1ROW)

This example is not really helpful, but I wanted to show as simple as possible something that works, and something that doesn't:

The only big problem I’ve run into is that calling enum_del to remove a value that’s still used in the database can cause all kinds of issues particularly in function definitions. At some point I’d like to modify the enum_del function to check the entire database to see if the value you wish to remove is in use anywhere, but I haven’t had time to research this to see if it’s possible. I’d also worry that it may be too performance intensive to perform this check automatically.

The functions above should not be used for several reasons. The add function does not cope with Oid wraparound, and the delete function is seriously incomplete. In general you should try hard to avoid manually mangling the catalogs. If you care enough about it enough, contribute a patch rather than hacking the system tables like this.

Note that the new 9.1 functionality is guaranteed to preserve the sort order of the existing elements and allows you to add the new element at any arbitrary place in the sort order. That’s something that’s quite impossible with 9.0 and previous, since the catalog didn’t have any support for explicit sort order (the Oids defined the sort order).