PostgreSQL: Deleting or re-ordering ENUM elements with no disruption

PostgreSQL manual says that it is not possible to delete an element from an ENUM type, nor change their order. However, there is a way to do it in a single transaction.

The theory

Suppose we have an ENUM type, and we want to drop an element, or change the order of its elements. Technically, there is no way to modify an ENUM type in those ways. But we can create a new type and delete the old one. If we do this in a transaction, following certain steps, other connections will not face any disruption – that is, from their perspective in no point in time the type will be missing.

The steps are:

  1. Start a transaction;
  2. Drop view that use the old type;
  3. Rename the old type, without deleting it (because it’s being used);
  4. Create a new type with the original name;
  5. Convert existing columns from the old type to the new one;
  6. Recreate views that use the old type;
  7. Drop the old type;
  8. Commit.

The practice

The SQL statements are the following:

START TRANSACTION;
-- for each view the uses the type:
DROP VIEW vw;

ALTER TYPE operating_system RENAME TO __old_operating_system;
CREATE TYPE operating_system AS ENUM (
        'Linux',
        '*BSD',
        'MacOS',
        'Windows'
);

-- for each column using this type:
ALTER TABLE tab ALTER col TYPE operating_system USING __old_operating_system::TEXT::operating_system;

-- recreate each view that we dropped:
CREATE VIEW vw;

DROP TYPE __old_operating_system;
COMMIT;

Caveats

If too many tables/columns use the type, the resulting transaction may be too long. In that case, you may want to use a different transaction for each column.

But if some tables are big, some of the transactions may still be a problem.

Reference

Related articles:

PostgreSQL documentation: