How to Use ENUM Data Types in PostgreSQL

Every schema eventually has a column that should only ever hold one of a small, fixed set of values — an order status, a user role, a subscription tier, a priority level. The instinctive options are usually a plain text column with an implied convention, or a separate lookup table joined in everywhere. PostgreSQL’s ENUM type offers a genuinely useful third option: a real type, enforced at the database level, that restricts a column to a fixed, ordered list of labels. This article covers how to create, use, modify, and think carefully about ENUM types in PostgreSQL.

What Is an ENUM Type?

An ENUM (enumerated type) is a custom data type consisting of a static, ordered set of text labels. A column declared with an ENUM type can only ever hold one of those exact labels — anything else is rejected outright by the database.

CREATE TYPE order_status AS ENUM ('pending', 'processing', 'shipped', 'delivered', 'cancelled');

That’s the entire type definition. It’s now usable anywhere a type is expected.

Using an ENUM in a Table

CREATE TABLE orders (
    order_id serial PRIMARY KEY,
    customer_name text NOT NULL,
    status order_status NOT NULL DEFAULT 'pending'
);

INSERT INTO orders (customer_name) VALUES ('Alice Johnson');
INSERT INTO orders (customer_name, status) VALUES ('Bob Smith', 'shipped');

Try inserting an invalid value:

INSERT INTO orders (customer_name, status) VALUES ('Carol Lee', 'in_transit');
-- ERROR:  invalid input value for enum order_status: "in_transit"

That rejection is the whole point — it happens at the database layer regardless of which application, script, or ad-hoc query is doing the writing, so you never end up with a stray typo value like 'shiped' silently sitting in your data.

ENUM Values Have an Inherent Order

This is one of the most useful and least appreciated features of Postgres ENUMs: the order you define the labels in becomes the type’s actual comparison order. This means you can sort and compare ENUM values meaningfully, not just alphabetically.

SELECT * FROM orders ORDER BY status;
-- rows come back in the order: pending, processing, shipped, delivered, cancelled
-- (the declaration order, not alphabetical order)

SELECT * FROM orders WHERE status > 'processing';
-- returns orders with status 'shipped', 'delivered', or 'cancelled'
-- based on their position in the declared list, not string comparison

This makes ENUMs genuinely useful for representing workflow stages or priority levels where “greater than” has real business meaning — something a plain text column can’t give you without extra casting logic.

Modifying an ENUM Type

Adding a New Value

ALTER TYPE order_status ADD VALUE 'returned';

By default, this appends the new value at the end of the list. You can control its position explicitly:

ALTER TYPE order_status ADD VALUE 'refunded' AFTER 'delivered';
ALTER TYPE order_status ADD VALUE 'awaiting_payment' BEFORE 'processing';

Important caveat: in PostgreSQL, ALTER TYPE ... ADD VALUE cannot run inside the same transaction block as a subsequent use of that new value. If you’re scripting a migration that adds a value and then immediately tries to use it in an INSERT within the same transaction, you’ll get an error. In modern PostgreSQL versions this restriction has been relaxed somewhat (you can add a value and use it in later commands within the same transaction, but not within the very same command that adds it) — check your specific version’s behavior, and when in doubt, split the ADD VALUE into its own committed transaction before using the new value.

Renaming a Value

ALTER TYPE order_status RENAME VALUE 'cancelled' TO 'canceled';

There Is No Direct “Drop Value”

This is the single biggest limitation of Postgres ENUMs, and it surprises almost everyone the first time they hit it: you cannot remove a value from an ENUM type directly. There’s no ALTER TYPE ... DROP VALUE. If a value is no longer valid going forward, your realistic options are:

1. Leave the value in the type definition but stop using it in application logic (simplest, and often perfectly fine).
2. Rebuild the type entirely: create a new type without the unwanted value, alter every column using the old type to the new one, then drop the old type. This requires updating all dependent tables and functions, and needs care around any data still using the removed value.

-- Example of rebuilding an ENUM type without a specific value
CREATE TYPE order_status_v2 AS ENUM ('pending', 'processing', 'shipped', 'delivered');

ALTER TABLE orders
    ALTER COLUMN status TYPE order_status_v2
    USING status::text::order_status_v2;

DROP TYPE order_status;
ALTER TYPE order_status_v2 RENAME TO order_status;

That USING status::text::order_status_v2 cast is required, and it will fail if any existing row still holds the value you’re trying to remove — you’ll need to migrate or clean up that data first.

Viewing Existing ENUM Values

SELECT enumlabel
FROM pg_enum
JOIN pg_type ON pg_type.oid = pg_enum.enumtypid
WHERE pg_type.typname = 'order_status'
ORDER BY enumsortorder;

This is genuinely useful for debugging, documentation generation, or building dynamic dropdown lists in application code from the source of truth in the database.

ENUM Types in Functions

CREATE OR REPLACE FUNCTION advance_order_status(p_order_id integer)
RETURNS order_status AS $$
DECLARE
    current_status order_status;
    next_status order_status;
BEGIN
    SELECT status INTO current_status FROM orders WHERE order_id = p_order_id;

    next_status := CASE current_status
        WHEN 'pending' THEN 'processing'
        WHEN 'processing' THEN 'shipped'
        WHEN 'shipped' THEN 'delivered'
        ELSE current_status
    END;

    UPDATE orders SET status = next_status WHERE order_id = p_order_id;
    RETURN next_status;
END;
$$ LANGUAGE plpgsql;

ENUM vs. Lookup Table: The Real Trade-off

This is the design decision worth spending real time on before committing, because switching later is genuinely annoying.

ENUMs are a good fit when:

• The set of values is genuinely static and rarely changes (statuses in a well-established workflow, fixed priority levels).
• You want compact storage — ENUM values are stored internally as 4-byte identifiers, cheaper than a text column or a joined foreign key lookup in many cases.
• You want meaningful ordering built into the type itself.
• You don’t need to attach additional metadata (a description, a color code, a sort priority override) to each value.

A lookup table (a normal table with a foreign key) is a better fit when:

• The set of values changes with any regularity — adding, and especially removing, ENUM values is operationally awkward, as shown above.
• You need to attach extra data to each value (a display label, a hex color, permission flags, translations for multiple languages).
• Different environments (staging vs. production) or different tenants in a multi-tenant system might need genuinely different value sets.
• You want to be able to query “all valid statuses” as actual queryable rows rather than reflecting into pg_enum.

A very common real-world pattern: start with an ENUM for something that feels genuinely fixed (order status, user role), and migrate to a lookup table later if the requirements evolve to need per-tenant customization or richer metadata. Knowing the migration path in advance (as shown above) makes that transition much less painful when it comes.

Practical Use Cases

1. Workflow / Status Fields

CREATE TYPE ticket_status AS ENUM ('open', 'in_progress', 'resolved', 'closed');

2. Fixed Role or Permission Levels

CREATE TYPE user_role AS ENUM ('viewer', 'editor', 'admin', 'owner');

Ordering here is genuinely useful: WHERE role >= 'editor' cleanly expresses “editor or higher privilege” without a separate numeric mapping table.

3. Priority or Severity Levels

CREATE TYPE priority_level AS ENUM ('low', 'medium', 'high', 'critical');

4. Fixed Categorical Data with Known, Stable Options

Days of the week, size options (S/M/L/XL) for a product catalog with a genuinely fixed set of sizes, and similar small, stable categorical domains.

Troubleshooting Common Issues

“Unsafe use of new value” errors when using a newly added ENUM value immediately. As covered above, this comes from trying to use a value added by ALTER TYPE ... ADD VALUE within the same transaction it was added in (or, in some version-specific edge cases, the same command). Commit the ADD VALUE in its own transaction first, then use the new value afterward.

Wanting to remove a value and finding there’s no direct way. This is expected Postgres behavior, not a bug — see the rebuild-the-type approach above.

ENUM values that don’t sort the way you expect. Double-check the actual declared order with the pg_enum query above — ordering is based on declaration order (and BEFORE/AFTER insertions), not alphabetical order, and it’s easy to lose track of the true order after several ALTER TYPE ... ADD VALUE statements over time.

Cross-schema or cross-database type conflicts. ENUM types, like all custom types, are schema-scoped. If you’re replicating schema across environments or databases, make sure the type — including its exact value order — is created identically everywhere, or migrations relying on specific orderings will behave inconsistently.

Casting frustration between ENUM and text. Comparisons between an ENUM column and a plain string literal usually work fine because Postgres can implicitly cast the literal, but comparing two different ENUM types, or building dynamic queries that construct ENUM values from variables, often requires explicit ::text and ::your_enum_type casts.

Best Practices

• Reserve ENUMs for value sets you’re genuinely confident are stable and unlikely to need frequent value removal.
• Document the intended meaning of each value directly, since ENUM labels themselves carry no metadata.
• Plan your value ordering deliberately from the start if you intend to use ordering comparisons (<, >) — retrofitting order later means careful use of BEFORE/AFTER insertions.
• Always run ALTER TYPE ... ADD VALUE in its own transaction, separate from any statement that immediately uses the new value.
• If you anticipate needing per-tenant customization, translations, or additional metadata down the line, consider starting with a lookup table instead of an ENUM, even if the current requirement looks simple.

ENUMs Across Schemas and Environments

Because ENUM types are schema-scoped custom types, keeping them consistent across development, staging, and production environments deserves real attention. A mismatch — say, staging has an extra ENUM value that production doesn’t — can cause deployment surprises when application code assumes a value exists that the production database hasn’t been migrated to include yet.

The safest pattern is to treat every CREATE TYPE ... AS ENUM and every subsequent ALTER TYPE ... ADD VALUE as a first-class migration, tracked in version control exactly like table schema changes, and applied through the same deployment pipeline — never as a manual, ad-hoc change against just one environment.

-- migration_001_create_order_status.sql
CREATE TYPE order_status AS ENUM ('pending', 'processing', 'shipped', 'delivered', 'cancelled');

-- migration_015_add_returned_status.sql
-- Must be committed on its own before any code references 'returned'
ALTER TYPE order_status ADD VALUE 'returned';

Splitting the ADD VALUE into its own migration file, deployed and committed before any code that depends on the new value ships, avoids both the same-transaction restriction covered earlier and the more general risk of application code referencing a value that doesn’t exist yet in a given environment.

ENUMs in Application Code

Most ORMs and database drivers map Postgres ENUM types to a native enum or constrained string type in the host language, but the mapping isn’t always automatic — you often need to either introspect the type or declare a matching enum on the application side manually.

-- Introspection query many ORMs use under the hood to discover valid ENUM values
SELECT t.typname, e.enumlabel, e.enumsortorder
FROM pg_type t
JOIN pg_enum e ON t.oid = e.enumtypid
WHERE t.typname = 'order_status'
ORDER BY e.enumsortorder;

A practical tip worth internalizing: whenever you add or rename an ENUM value at the database level, that change needs to be mirrored in the corresponding application-side enum definition in the same deployment. These two representations — one at the database layer, one in application code — are logically the same source of truth expressed twice, and letting them drift apart is a common, entirely preventable source of “invalid input value for enum” errors in production that only show up after a partial deployment.

A Complete Example: Ticket Workflow with Enforced Transitions

Combining ENUM ordering with a trigger is a genuinely powerful pattern for enforcing valid state transitions directly at the database level, rather than trusting application code to always get the transition logic right:

CREATE TYPE ticket_status AS ENUM ('open', 'in_progress', 'resolved', 'closed', 'reopened');

CREATE TABLE support_tickets (
    ticket_id serial PRIMARY KEY,
    subject text NOT NULL,
    status ticket_status NOT NULL DEFAULT 'open'
);

CREATE OR REPLACE FUNCTION validate_status_transition()
RETURNS trigger AS $$
BEGIN
    IF OLD.status = 'closed' AND NEW.status NOT IN ('reopened') THEN
        RAISE EXCEPTION 'Cannot transition from closed to %', NEW.status;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER enforce_ticket_status_transitions
BEFORE UPDATE ON support_tickets
FOR EACH ROW
WHEN (OLD.status IS DISTINCT FROM NEW.status)
EXECUTE FUNCTION validate_status_transition();

UPDATE support_tickets SET status = 'closed' WHERE ticket_id = 1;
UPDATE support_tickets SET status = 'resolved' WHERE ticket_id = 1;
-- ERROR:  Cannot transition from closed to resolved

This combination — a well-ordered ENUM plus a lightweight trigger enforcing legitimate transitions — gives you a genuinely robust workflow guarantee that’s enforced no matter which application, script, or admin tool performs the update, without needing to duplicate the transition rules in every codebase that touches the table.

Comparing ENUMs to CHECK Constraints on Text Columns

A question that comes up frequently: why not just use a plain text column with a CHECK (status IN ('pending', 'shipped', ...)) constraint instead of a real ENUM type? Both approaches genuinely restrict the column to a fixed set of values, so the choice comes down to a few practical differences.

-- The CHECK constraint alternative
CREATE TABLE orders_alt (
    order_id serial PRIMARY KEY,
    status text NOT NULL CHECK (status IN ('pending', 'processing', 'shipped', 'delivered', 'cancelled'))
);

A CHECK-constrained text column is simpler to alter — adding or removing a valid value is a single ALTER TABLE ... DROP CONSTRAINT and ADD CONSTRAINT, without the ENUM-specific restrictions around removing values or the same-transaction limitation on adding them. It’s also trivially reusable in raw SQL contexts, and doesn’t require any type-level coordination.

An ENUM, on the other hand, gives you genuine ordering (useful for workflow or severity comparisons), more compact storage (4 bytes versus the variable length of a text value), and reusability across multiple tables without repeating the same list of valid values in every table’s constraint definition, keeping them in sync becomes the developer’s responsibility rather than the database’s.

In practice, many experienced Postgres users lean toward CHECK constraints on text columns specifically because the ENUM value-removal limitation is annoying enough in practice to outweigh ENUM’s advantages, unless the ordering property is something they actually rely on. It’s a genuinely reasonable position — know both options are available, and pick based on whether you need ordering and cross-table reuse (lean ENUM) or maximum flexibility to change the valid set over time (lean CHECK constraint).

Wrapping Up

ENUM types give PostgreSQL developers a genuinely useful middle ground between “loose text column with an informal convention” and “full lookup table with a join.” They enforce valid values at the database level, carry meaningful built-in ordering, and store compactly. The trade-off to keep in mind is operational flexibility — adding values is easy, removing them is deliberately awkward by design. Know that trade-off going in, choose ENUMs for value sets that are genuinely stable, and reach for a lookup table when you expect the set of valid values to evolve or need richer metadata over time.