ENUM Types

Create enumerated types for columns with a fixed set of allowed values.

Overview

PostgreSQL ENUM types define a list of allowed string values for a column. They provide: type safety (only listed values are accepted), compact storage (stored as integers internally), and natural ordering (values compare in the order they were defined). ENUMs are useful for status fields, categories, and any column with a small, fixed set of values. ENUMs can be added to with ALTER TYPE ... ADD VALUE but cannot easily have values removed consider TEXT with a CHECK constraint for more flexible validation.

Key Points

•CREATE TYPE name AS ENUM ('val1', 'val2', ...)
•Values are case-sensitive and compared in definition order
•ALTER TYPE name ADD VALUE 'new_val' (can specify position)
•Cannot remove values from an ENUM without DROP TYPE and recreation
•ENUMs can be used in CHECK, CASE, and comparison expressions
•Alternative: TEXT + CHECK constraint for easier schema evolution

Examples

Creating and using ENUM

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

CREATE TABLE orders (
  id     SERIAL PRIMARY KEY,
  status order_status NOT NULL DEFAULT 'pending'
);

-- ENUM ordering (defined order, not alphabetical)
SELECT * FROM orders WHERE status < 'shipped';   -- pending, processing

-- Add a new value
ALTER TYPE order_status ADD VALUE 'returned' AFTER 'delivered';

ENUM Types

Overview

Key Points

Examples

Exercises

Knowledge Check

How are ENUM values ordered?