Finally, on 25th of October, Tom Lane committed patch which does:
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:
CREATE TYPE mood AS ENUM ( 'sad', 'ok', 'happy' );
And now I can use the values with casts to enum:
select 'sad'::mood; mood ------ sad (1 row)
This example is not really helpful, but I wanted to show as simple as possible something that works, and something that doesn't:
select 'dead'::mood; ERROR: invalid input value for enum mood: "dead" LINE 1: select 'dead'::mood; ^
While is might be debated whether dead is a mood, the fact is that our enum doesn't allow it. But if we'd really need, we can now:
alter type mood add 'dead' BEFORE 'happy';
And now the datatype looks like:
\dT+ mood List of data types Schema | Name | Internal name | Size | Elements | Description --------+------+---------------+------+----------+------------- public | mood | mood | 4 | sad +| | | | | ok +| | | | | dead +| | | | | happy | (1 row)
And so, I can:
select 'dead'::mood; mood ------ dead (1 row)
It's great. ENUM still doesn't solve all needs (what if we no longer support users which are dead? There is no way to remove the value from ENUM, but it's a big step towards making ENUMs fully useful.
9 thoughts on “Waiting for 9.1 – adding values to enums”
so we will get remove (drop) of enum values in (9.1-8.3) 3 releases from 9.1, so 9.4 🙂
It is worth noting that Andrew Dunstan proposed to use:
ALTER TYPE enumtype ADD LABEL ‘newlabel’
And I completely agree with him, just like some other guys.
These two functions were borrowed from another web page (can’t remember where, but I’m sure a google search will find the original author) and modified to take schema’s into account:
Both functions should work in 8.3, 8.4 and 9.0. Usage would be as follows:
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.
Apologies about the formatting on that last post – if you are able please could you tidy up the new lines? Thanks!
I wrote post with poll about syntax of this feature: http://pgolub.wordpress.com/2010/10/28/poll-alter-type-enumtype-add-what-newlabel/
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).
‘ALTER TYPE … ADD cannot be executed from a function or multi-command string’
“ALTER TYPE … ADD VALUE (the form that adds a new value to an enum type) cannot be executed inside a transaction block.”
We got serious work while being on 9.0 with enums, 9.1 won’t really help us because of this limitation.
use this syntax :
alter type cve_severity ADD VALUE ‘aaa’;
Comments are closed.