An enum is a data type whose values come from a fixed list you declare: CREATE TYPE name AS ENUM ('a', 'b', 'c'). A column of that type accepts only those labels and sorts them in declared order, not alphabetically.
The rows come back in the order new, paid, shipped, delivered. Inserting a label that is not in the list, such as 'lost', fails with invalid input value for enum order_status: "lost".
Compare and filter by position
Because the values are ordered, <, > and BETWEEN compare positions in the list. "Paid or any later status" becomes one condition:
Orders 1, 3 and 4 match. min(status) and max(status) also follow the declared order.
List the values of an enum
enum_range(NULL::type) returns every label as an array, and unnest turns it into rows:
enum_first(NULL::mood) and enum_last(NULL::mood) return the first and last label. In psql, \dT+ mood shows the type with its values.
Add or rename a value
ALTER TYPE ... ADD VALUE appends a label, or places it with BEFORE or AFTER. RENAME VALUE changes a label, and every row using it follows, because rows store a reference to the label rather than its text.
The list is now pending, paid, packed, shipped, delivered. IF NOT EXISTS makes adding a label that is already there a no-op.
PostgreSQL cannot remove a value or reorder existing ones. Instead, create a new type, convert the column to it, and drop the old one:
CREATE TYPE order_status_new AS ENUM ('pending', 'paid', 'shipped');
UPDATE orders SET status = 'shipped' WHERE status = 'delivered';
ALTER TABLE orders
ALTER COLUMN status DROP DEFAULT,
ALTER COLUMN status TYPE order_status_new USING status::text::order_status_new;
DROP TYPE order_status;
ALTER TYPE order_status_new RENAME TO order_status;
The default is dropped first because it is typed as the old enum. Set it again afterwards.
Enum vs a CHECK constraint
A text column with a CHECK constraint gives the same protection, and its list can be changed with a plain ALTER TABLE:
Here the sort is alphabetical: new, then paid.
| Enum | text + CHECK | |
|---|---|---|
| Sort order | Declared order | Alphabetical |
| Storage | 4 bytes | Length of the text |
| Remove a value | New type and a column conversion | Replace the constraint |
| Reuse in several tables | One type | Repeat the constraint |
Pick an enum for a short, stable list whose order means something, like statuses or sizes. Pick CHECK, or a lookup table with a foreign key, when the list changes often or values get removed. The enum chapter of the manual covers the remaining details.
Frequently Asked Questions
How do I delete a value from a PostgreSQL enum?
There is no ALTER TYPE ... DROP VALUE. Create a new enum without the value, convert the column with ALTER TABLE ... ALTER COLUMN ... TYPE new_type USING col::text::new_type after updating rows that use it, then drop the old type.
How much space does an enum value take?
4 bytes per value on disk, whatever the length of its label. Labels are case sensitive and can be up to 63 bytes long.
Can I use ALTER TYPE ADD VALUE inside a transaction?
Yes, since PostgreSQL 12. The new value cannot be used until that transaction has committed.
How do I compare an enum column with text?
A string literal is converted to the enum automatically, so WHERE status = 'paid' works. A text column or parameter needs a cast, status = $1::order_status, or compare as text with status::text = $1.