PostgreSQL supports custom enum types, which is awesome. However, extending them is kind of a pain, since there is no command to just add a new value to the enum.
Thanks to this StackOverflow answer, there are two alternate ways to still get what you want. One works by modifying the
pg_enum, which strikes me as a bit of a bad idea, since those tables are under Postgres’ direct control.
The other one works by adding a new type, and adding a new column, then copying over all the old values, and finally dropping the old columns and types (via taksofan):
This works well enough for my purposes, but will probably not do so well with very large tables.
If you want to change the enum type used in a large table, then the method described above will very likely take a long time to finish.
The faster, safe way
The first one works like this:
- You create your new, extended enum type
- You alter your column to use that new enum type. This requires a single bulk rewrite of the table, but this is much faster than copying over the old values into a new value.
- Drop the old value
Or, in SQL:
The even faster, but possibly very dangerous, way
The second one is decidedly more dangerous, so make sure you test everything thouroghly.
First, for this to work, you must ensure that no OID wraparound has happened. How can you tell if an OID wraparound has happened? Execute this query:
Then make sure if the returned OID is larger than both the smallest and largest value of the enum.
And secondly, this messes around in PostgreSQL internals, so there might be other side effects. Use this at your own risk.