PostgreSQL: Working with ENUM types

Sometimes a text column should not contain free texts, but only values from a predefined list. PostgreSQL allows to achieve this by creating an enumeration – or an ENUM type. This allows validation on input, and makes the values more efficient.

Creating an ENUM

Each enumeration is a type. To create ENUM types:

CREATE TYPE ORDER_STATUS AS ENUM
    ('new', 'sent', 'delivered', 'cancelled');
CREATE TYPE TASK_STATUS AS ENUM
    ('new', 'in progress', 'done');

Creating columns of an ENUM type

We can then use these types multiple times, every time we need them. We can make columns of these types NULL or NOT NULL, assign them a DEFAULT value, etc.

CREATE TABLE product_order (
    id SERIAL PRIMARY KEY,
    status ORDER_STATUS NOT NULL DEFAULT 'new',
    ...
);

Querying an ENUM type

In general, we can consider an ENUM column as a case-sensitive string:

SELECT count(*)
    FROM product_order
    WHERE status = 'new';

The way an ENUM is sorted is different than a string though. The order of elements is defined by the CREATE TYPE statement.

SELECT count(*)
    FROM product_order
    WHERE status < 'delivered';

This query returns the number of 'new' and 'sent' orders.

Getting information about an ENUM type

User-defined types are listed in pg_type. Values forming each ENUM are listed in pg_enum.

To get all ENUM types and their elements:

SELECT
        t.name AS type_name,
        e.enumlabel AS element
    FROM pg_type t
    INNER JOIN pg_enum e
        ON t.oid = e.enumtypid
    WHERE t.typtype = 'e'
    ORDER BY t.name, e.enumsortorder;

Modifying an ENUM type

To add an element:

ALTER TABLE ORDER_STATUS
    -- you can use AFTER rather than BEFORE
    ADD VALUE 'invalid' BEFORE 'cancelled';

To modify an element:

ALTER TABLE ORDER_STATUS
    RENAME VALUE 'invalid' TO 'rejected';

To rename a type:

ALTER TABLE ORDER_STATUS
    RENAME TO PRODUCT_ORDER_STATUS;

It is not possible to delete a value from the list.

Multiple changes can be applied together:

ALTER TABLE ORDER_STATUS
    ADD VALUE x,
    RENAME VALUE y TO z;

Eliminating an ENUM type

To delete an unused ENUM type:

DROP TYPE ORDER_STATUS;

If the type is currently used by columns or stored routines parameters, this statement will fail.

To delete a type and all the columns and stored routines parameters that are using it:

DROP TYPE ORDER_STATUS CASCADE;

Reference

Deleting or re-ordering ENUM elements with no disruption

PostgreSQL documentation: