Enumerated Types

An enum type restricts a column to a fixed, named set of values — you define the allowed values once, and PostgreSQL enforces that only those values can ever be stored. It's a clean way to model a genuinely fixed set of states or categories, like an order status or a mood.

Defining and using an enum

Creating an enum type

SQL

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');

CREATE TABLE journal_entries (
    entry_id   SERIAL PRIMARY KEY,
    entry_text TEXT NOT NULL,
    entry_mood mood NOT NULL DEFAULT 'ok'
);

INSERT INTO journal_entries (entry_text, entry_mood)
VALUES ('Shipped the release today', 'happy');

INSERT INTO journal_entries (entry_text, entry_mood)
VALUES ('Bug report', 'grumpy');
-- error: invalid input value for enum mood: "grumpy"

entry_id |         entry_text          | entry_mood
---------+------------------------------+------------
       1 | Shipped the release today   | happy

Why use an enum instead of a CHECK constraint?

You could enforce the same fixed value set with a TEXT column and a CHECK (entry_mood IN ('sad', 'ok', 'happy')) constraint, and it would work correctly. An enum type has two advantages over that approach: it's more storage-efficient (an enum value is stored internally as a small integer, not the full text of the label), and it's self-documenting — the valid values live in one named type that every column using it automatically shares, instead of being repeated inside a CHECK expression on each table.

Changing an enum's values later

Adding a new value

SQL

ALTER TYPE mood ADD VALUE 'ecstatic';
ALTER TYPE mood ADD VALUE 'meh' BEFORE 'ok';

Warning

Adding a new enum value with `ALTER TYPE ... ADD VALUE` is easy and safe. **Removing** a value, or reordering existing values in a meaningful way, is much harder — PostgreSQL has no direct `DROP VALUE`. In practice, removing a value means creating a new enum type without it, updating every column that uses the old type to the new one, and dropping the old type — a genuine migration, not a one-line `ALTER`. Plan enum value sets with the expectation that values may need to be **added** over time, but should rarely need to be **removed**.

Note

If you expect a value set to change frequently — new categories added by end users, values that need descriptions or other attributes, or values that might genuinely need to be renamed or retired — a lookup table with a foreign key is usually more flexible than an enum. Reserve enums for value sets that are genuinely fixed by the nature of the domain (the seven days of the week, a small set of internal status codes you control) rather than anything driven by changing business requirements.

CREATE TYPE name AS ENUM (...) defines a fixed, named set of allowed values.
An enum column rejects any value outside that set, enforced by PostgreSQL itself.
Enums are more storage-efficient and more self-documenting than an equivalent CHECK ... IN (...) constraint.
ALTER TYPE ... ADD VALUE adds new values easily, but removing or reordering values is difficult and often requires recreating the type.
For value sets that change frequently, a lookup table with a foreign key is usually the more flexible design.