PostgreSQL enums: create, order, add, and rename values

A PostgreSQL enum is a named data type containing an ordered set of allowed labels. Create it with CREATE TYPE ... AS ENUM, then use that type for a table column. It is a useful choice for a small, stable vocabulary such as an order status, provided you are comfortable treating changes to that vocabulary as schema changes.

This guide builds an order-status example and tests its behavior on PostgreSQL 18.6. Our 29 local checks cover accepted and rejected values, sorting, NULL, transaction boundaries, renaming, defaults, and type dependencies. These are correctness checks in an isolated database, not a production migration or performance benchmark.

Create an enum and use it in a table

Use a disposable database named enum_demo, with permission to create a schema. It must not already contain enum_lab. Save each numbered block as its named SQL file and run the files in order, in separate psql invocations. For example: psql -X -qAt -v ON_ERROR_STOP=1 -d enum_demo -f 01-create.sql. Add the connection options your database needs.

The displayed results use psql's unaligned, tuples-only format, with a vertical bar between columns. Files 03 and 04 intentionally fail; continue with the next file in a new invocation. Do not wrap the whole walkthrough in a single transaction or use psql's --single-transaction option.

Save this as 01-create.sql:

CREATE SCHEMA enum_lab;
CREATE TYPE enum_lab.order_status AS ENUM ('new', 'paid', 'shipped');
CREATE TABLE enum_lab.orders (
    id integer PRIMARY KEY,
    status enum_lab.order_status NOT NULL DEFAULT 'new'
);
INSERT INTO enum_lab.orders (id, status)
VALUES (1, 'shipped'), (2, 'new'), (3, 'paid');
INSERT INTO enum_lab.orders (id) VALUES (4);
SELECT id, status FROM enum_lab.orders ORDER BY id;

1|shipped
2|new
3|paid
4|new

The type lives in enum_lab, so the column definition uses its qualified name. The status column accepts new, paid, and shipped. Row 4 omits status and receives new from the default. The primary key and NOT NULL constraint remain separate rules; choosing an enum does not automatically require a value.

The PostgreSQL enum documentation explains the type's ordered labels and restrictions. Keep the database labels stable and put translated display names in your application. An English label such as paid can represent the same database state regardless of the language shown to a user.

Inspect the allowed values and sort order

Run 02-order.sql:

SELECT id, status FROM enum_lab.orders ORDER BY status, id;
SELECT id, status FROM enum_lab.orders
ORDER BY status::text COLLATE "C", id;
SELECT min(status), max(status) FROM enum_lab.orders;
SELECT enum_range(NULL::enum_lab.order_status);

2|new
4|new
3|paid
1|shipped
2|new
4|new
3|paid
1|shipped
new|shipped
{new,paid,shipped}

The first query sorts the enum itself. The second converts it to text and applies the C collation for a reproducible text-order comparison. They happen to agree for this initial set. Later, adding packed between paid and shipped will make the difference visible.

The minimum and maximum are new and shipped. The final expression lists the entire vocabulary, including values that might have no rows. Passing a typed NULL tells enum_range which enum to inspect; it does not add NULL to the allowed labels. The enum function reference also covers enum_first, enum_last, and bounded ranges.

Decide whether the order has a useful meaning before relying on comparisons such as status > 'paid'. A canceled or refunded status may not belong on one linear progression. An enum constrains individual values; it does not enforce which transition from one status to another is permitted.

Reject invalid labels without confusing them with NULL

Run 03-invalid.sql as a separate invocation:

INSERT INTO enum_lab.orders (id, status) VALUES (5, 'Paid');

Our test rejected Paid with SQLSTATE 22P02. The declared label is paid. Separate checks also rejected paid with a trailing space and the undeclared label cancelled. All four existing rows remained unchanged.

Inserting NULL into orders.status failed with SQLSTATE 23502 because of NOT NULL. A separate nullable column using the same enum accepted NULL. Decide explicitly whether missing status is meaningful; the type and the column constraint answer different questions.

If data arrives from another service, normalize it deliberately at the boundary and reject unrecognized states. Do not silently map every spelling error to new. For bulk files, validate the source values before loading them; a type conversion error should be investigated rather than hidden.

Add an enum value and respect the commit boundary

This deliberately failing 04-unsafe-add.sql shows a migration trap:

BEGIN;
ALTER TYPE enum_lab.order_status ADD VALUE 'packed' BEFORE 'shipped';
INSERT INTO enum_lab.orders (id, status) VALUES (5, 'packed');
COMMIT;

Adding packed to the existing type succeeds inside the transaction, but the following INSERT fails with SQLSTATE 55P04: the new label is not yet safe to use. With ON_ERROR_STOP, psql exits before COMMIT. The connection closes and rolls back the transaction. Our next connection confirmed that packed was absent and ID 5 had not been inserted. In an interactive session, issue ROLLBACK after the error before continuing.

Now run 05-add.sql. It commits the schema change before inserting the new label:

BEGIN;
ALTER TYPE enum_lab.order_status ADD VALUE 'packed' BEFORE 'shipped';
COMMIT;
INSERT INTO enum_lab.orders (id, status) VALUES (5, 'packed');
SELECT enum_range(NULL::enum_lab.order_status);
SELECT id, status FROM enum_lab.orders ORDER BY status, id;

{new,paid,packed,shipped}
2|new
4|new
3|paid
5|packed
1|shipped

The declared order is now new, paid, packed, shipped. ID 5 sorts between paid and shipped. Our separate text-order query put packed before paid under the C collation, confirming that enum order and label spelling are different.

The ALTER TYPE reference documents ADD VALUE, BEFORE/AFTER placement, and the commit requirement. Without a placement clause, a new value goes at the end. Use the type owner, or an appropriately authorized administrative role, for the schema change.

Check how your migration tool wraps statements. A file boundary alone is insufficient if the tool places several files in one transaction. Commit the addition first, then use the value in a later transaction. Coordinate application releases so readers can handle the new label before writers start sending it.

In an extra check, ADD VALUE IF NOT EXISTS for the already present packed label left its position unchanged, even when the statement requested AFTER shipped. The option avoids a duplicate-add error; it does not move an existing label.

Rename a label and check existing data

Run 06-rename.sql:

ALTER TYPE enum_lab.order_status RENAME VALUE 'new' TO 'pending';
SELECT id, status FROM enum_lab.orders ORDER BY id;
SELECT enum_range(NULL::enum_lab.order_status);
INSERT INTO enum_lab.orders (id) VALUES (6);
SELECT id, status FROM enum_lab.orders WHERE id = 6;

1|shipped
2|pending
3|paid
4|pending
5|packed
{pending,paid,packed,shipped}
6|pending

Rows 2 and 4 now display pending, without updating each row. The label retains its position and internal enum identity. The existing default also produces pending for row 6. Our test verified those results directly.

The old spelling new is no longer accepted, and renaming paid to the already existing pending label fails. Update application constants, validation, reporting filters, and integration contracts along with the schema change. Existing rows displaying the new spelling does not mean an older application will understand it.

Two independently created enum types remain different types even if their labels match. Our cross-type equality test failed with SQLSTATE 42883. Explicitly casting both sides to text allowed a label comparison, but that comparison intentionally drops the distinction between the two enum types.

Choose between an enum, a CHECK constraint, and a lookup table

• Use an enum when the vocabulary is small and stable, several columns should share one named type, and its declared order is meaningful.
• Consider text with a CHECK constraint when the restriction belongs to one table and you prefer to manage it as a column rule. Plan how constraint changes will validate existing rows.
• Consider a lookup table with a foreign key when each choice needs descriptions, translated labels, activation dates, or other attributes that change as data. References and deletion rules still need deliberate design.

PostgreSQL does not provide a simple enum DROP VALUE or a command to reorder existing labels. Replacing a type requires a planned migration of dependent columns and other objects, including how existing values will be mapped. Do not edit system catalogs to force the change. Before a consequential schema migration, prepare and verify recovery using the PostgreSQL backup and restore guide.

Adding a status also does not grant permission to change it. If access rules differ by user or tenant, handle them separately from the allowed-value type. Avoid choosing an enum because of an assumed speed advantage: this walkthrough measures behavior, not workload performance.

Remove the example objects

After completing the exercise, run 07-cleanup.sql in the disposable database:

DROP TABLE enum_lab.orders;
DROP TYPE enum_lab.order_status;
DROP SCHEMA enum_lab;

The table is removed before its type. Our negative test confirmed that plain DROP TYPE refuses to remove the type while the orders column depends on it. The cleanup uses explicit object names and does not use CASCADE.

The downloadable verification package contains the SQL, captured output, and test harness. The harness creates its own password-protected local cluster, removes the exercise objects, stops the server, and deletes its temporary password file. It does not connect to an existing application database. For a separate container-based learning environment, see our PostgreSQL with Docker Compose guide.