Enum

Introduction to the PostgreSQL enum data type

An 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 — this means you can sort and compare enum values with standard operators (>, <, =, etc.).

Enum values are case-sensitive: 'high' and 'HIGH' are different values.

Create an enum type with:

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

Creating and using an enum

-- Define the enum type
CREATE TYPE priority AS ENUM('low', 'medium', 'high');

-- Use it as a column type
CREATE TABLE requests (
  id           INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title        VARCHAR(255) NOT NULL,
  priority     priority NOT NULL,
  request_date DATE NOT NULL
);

-- Insert rows
INSERT INTO requests (title, priority, request_date) VALUES
  ('Create an enum tutorial', 'high',   '2019-01-01'),
  ('Review the enum tutorial', 'medium', '2019-01-01'),
  ('Publish the tutorial',     'low',    '2019-01-01')
RETURNING *;

Sorting and filtering by enum value

-- Sort by enum order (low < medium < high)
SELECT * FROM requests ORDER BY priority;

-- Filter requests with priority greater than 'low'
SELECT * FROM requests
WHERE priority > 'low'
ORDER BY priority;

-- Attempting an invalid value raises an error
INSERT INTO requests (title, priority, request_date)
VALUES ('Urgent task', 'urgent', '2019-01-02');
-- ERROR: invalid input value for enum priority: "urgent"

Adding new values to an enum

-- Append a new value at the end
ALTER TYPE priority ADD VALUE 'urgent';

-- Insert before or after an existing value
ALTER TYPE priority ADD VALUE 'critical' AFTER 'high';

-- Conditionally add
ALTER TYPE priority ADD VALUE IF NOT EXISTS 'urgent';

-- Verify the current values
SELECT enum_range(null::priority);
-- {low,medium,high,critical,urgent}

Getting the first and last enum values

SELECT
  enum_first(NULL::priority) AS first_value,
  enum_last(NULL::priority)  AS last_value;
-- low | urgent

Renaming an enum value

ALTER TYPE priority RENAME VALUE 'urgent' TO 'very high';

SELECT enum_range(null::priority);
-- {low,medium,high,critical,"very high"}

Production tips

• Use enums for small, stable value sets (status codes, priority levels, days of week) — they provide type safety and natural ordering with no extra joins.
• Avoid enums for frequently changing value sets — adding a value requires a DDL change, not just an INSERT into a lookup table.
• Enum values are case-sensitive — standardize on lowercase or uppercase 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.

Reference: PostgreSQL documentation — Enumerated types.