Enum Data Type in PostgreSQL

A PostgreSQL enum type restricts a column to a fixed, ordered set of string values. The ordering of values is the order in which they are declared — which means you can sort and compare enum values with standard operators (>, <, =, BETWEEN). Enum values are case-sensitive.

Syntax

CREATE TYPE enum_name AS ENUM('value1', 'value2', 'value3');

After creating the type, use it as a column type:

column_name enum_name

Practical Example

Model order fulfillment status with an enum:

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

CREATE TABLE orders (
  id          SERIAL PRIMARY KEY,
  product_sku VARCHAR(50)  NOT NULL,
  quantity    INTEGER      NOT NULL CHECK (quantity > 0),
  status      order_status NOT NULL DEFAULT 'pending',
  placed_at   TIMESTAMPTZ  NOT NULL DEFAULT now()
);

INSERT INTO orders (product_sku, quantity, status)
VALUES
  ('SKU-001', 2, 'delivered'),
  ('SKU-002', 1, 'shipped'),
  ('SKU-003', 5, 'processing'),
  ('SKU-004', 3, 'pending');

Sorting and Filtering by Enum Order

Enum comparison uses the declaration order, not alphabetical order:

-- Sort by fulfillment progress (pending first, delivered last)
SELECT id, product_sku, status
FROM orders
ORDER BY status;

-- Find orders that have not yet shipped
SELECT id, product_sku, status
FROM orders
WHERE status < 'shipped'
ORDER BY status;

 id | product_sku |   status
----+-------------+------------
  4 | SKU-004     | pending
  3 | SKU-003     | processing

Invalid values raise an error immediately:

INSERT INTO orders (product_sku, quantity, status)
VALUES ('SKU-005', 1, 'lost_in_transit');
-- ERROR: invalid input value for enum order_status: "lost_in_transit"

Adding New Enum Values

-- Append at the end
ALTER TYPE order_status ADD VALUE 'refunded';

-- Insert before or after a specific value
ALTER TYPE order_status ADD VALUE 'partially_shipped' AFTER 'processing';

-- Conditionally add (no error if already exists)
ALTER TYPE order_status ADD VALUE IF NOT EXISTS 'refunded';

-- Inspect current values
SELECT enum_range(NULL::order_status);

Inspecting Enum Values

-- First and last values
SELECT
  enum_first(NULL::order_status)  AS first_status,
  enum_last(NULL::order_status)   AS last_status;

-- All values from the catalog
SELECT enumlabel, enumsortorder
FROM pg_enum
JOIN pg_type ON pg_type.oid = pg_enum.enumtypid
WHERE pg_type.typname = 'order_status'
ORDER BY enumsortorder;

Renaming an Enum Value

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

SELECT enum_range(NULL::order_status);

Testing with Vela

Adding or renaming enum values in production carries risk — application code that serializes or deserializes enum values may not recognize new names. Before running ALTER TYPE ... ADD VALUE on a live database, test on a database branch with production data: update your application code to handle the new value, deploy to a branch environment, run your full test suite, and only then promote the DDL change to production.

Production Tips

• Use enums for small, stable value sets (status codes, priority levels, days of week) — they provide type safety and natural ordering without extra joins.
• Avoid enums for frequently changing value sets — adding a value requires a DDL change, whereas a lookup table only needs an INSERT.
• Enum values are case-sensitive — standardize on lowercase consistently to avoid subtle bugs.
• For portability across databases, use a VARCHAR column with a CHECK constraint or a lookup table instead of an enum.
• New enum values added with ADD VALUE cannot be used in the same transaction where they were added — commit the ADD VALUE first.