Improving ENUM handling

The possibility to create ENUM types (CREATE TYPE ... AS ENUM) has been around for quite some time now. In PostgreSQL 12, some improvements have been made to make sure that the type can be used even more efficiently. Let's create a simple type first and see how this works:

test=# CREATE TYPE currency AS ENUM ('USD', 'EUR', 'GBP');
CREATE TYPE

For the sake of simplicity, I have created a data type storing a couple of currencies. Mind that the order in the ENUM type does matter. If you order by a currency column, you will notice that the order returned by PostgreSQL is exactly as specified in the ENUM type. 

What has been added in PostgreSQL is the ability to modify an ENUM type inside a single transaction. The following example shows how that works:

test=# BEGIN;
BEGIN
test=# ALTER TYPE currency ADD VALUE 'CHF' AFTER 'EUR';
ALTER TYPE
test=# SELECT 'USD'::currency;
 currency 
----------
 USD
(1 row)

However, there is one restriction: as you can see, the old ENUM values can be used inside the transaction directly. However, the new ones are not available within the same transaction:

test=# SELECT 'CHF'::currency;
ERROR: unsafe use of new value "CHF" of enum type currency
LINE 1: SELECT 'CHF'::currency;
 ^
HINT: New enum values must be committed before they can be used.
test=# COMMIT;
ROLLBACK

The transaction will error out if we want to access the new value inside the transaction.