Menu

PostgreSQL Enum Types: CREATE TYPE AS ENUM

An enum type is a fixed, ordered list of allowed values, created with CREATE TYPE ... AS ENUM. Covers adding and renaming values, listing them, sorting, and when a CHECK constraint is the better choice.

This page includes runnable editors. Edit, run, and see output instantly.

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.

Enumtext + CHECK
Sort orderDeclared orderAlphabetical
Storage4 bytesLength of the text
Remove a valueNew type and a column conversionReplace the constraint
Reuse in several tablesOne typeRepeat 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.

Coddy programming languages illustration

Learn to code with Coddy

Get started