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:
This example is not really helpful, but I wanted to show as simple as possible something that works, and something that doesn't:
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:
List of data types
Schema | Name | Internal name | Size | Elements | Description
public | mood | mood | 4 | sad +|
| | | | ok +|
| | | | dead +|
| | | | happy |
And so, I can:
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.