Item 5 – Use ENUM Types for Fixed Sets of Values

by

Table of Contents

The Problem: String Columns Without Constraints

You’re building an order management system. Orders have statuses like “pending”, “processing”, “shipped”, and “delivered”. The naive approach uses a TEXT column:

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    status TEXT NOT NULL,
    total NUMERIC(10, 2) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

What could go wrong?

Problem 1: Typos Create Invalid Data

-- Intended: 'shipped'
INSERT INTO orders (id, status, total) VALUES (1, 'shipped', 99.99);
-- ✓ Success

-- Typo: 'shippd' (missing 'e')
INSERT INTO orders (id, status, total) VALUES (2, 'shippd', 149.99);
-- ✓ Success! Database accepts invalid status.

-- Typo: 'Shipped' (wrong case)
INSERT INTO orders (id, status, total) VALUES (3, 'Shipped', 199.99);
-- ✓ Success! Now you have inconsistent casing.

Problem 2: No Discoverability

New developers (or AI assistants!) don’t know what valid statuses are:

-- What are the valid statuses?
SELECT DISTINCT status FROM orders;
-- pending
-- processing
-- shipped
-- shippd      ← Typo!
-- Shipped     ← Casing inconsistency!
-- delivered
-- complete    ← Is this the same as 'delivered'?

Problem 3: Difficult to Refactor

You need to rename “processing” to “fulfilling”:

-- Must update ALL rows
UPDATE orders SET status = 'fulfilling' WHERE status = 'processing';

-- Must update ALL application code
if order.status == 'processing':  # Old code breaks
    ...

-- Must update ALL documentation
-- Must notify ALL developers

Problem 4: Index Inefficiency

PostgreSQL can’t optimize storage or queries for limited value sets:

-- TEXT column stores full string for each row
-- 'processing' = 10 bytes × 1,000,000 rows = 10MB
-- Even though there are only 4 possible values!

Problem 5: No Type Safety

Application code can set any string value:

# Python code
order.status = 'proccessing'  # Typo - no compile-time error
order.save()  # Database accepts it!

# JavaScript code
order.status = 'shiped';  # Typo - no IDE warning
await order.save();  # Database accepts it!

The Naive Solution: CHECK Constraints

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    status TEXT NOT NULL,
    total NUMERIC(10, 2) NOT NULL,
    CHECK (status IN ('pending', 'processing', 'shipped', 'delivered'))
);

This prevents invalid values:

INSERT INTO orders (id, status, total) VALUES (1, 'invalid');
-- ✗ ERROR: new row violates check constraint "orders_status_check"

But CHECK constraints still have problems:

  1. No discoverability – Developers must read constraint definition
  2. Application doesn’t know values – No autocomplete or type safety
  3. Difficult to query – Can’t easily list all possible statuses
  4. Still stores full strings – No storage optimization
  5. Case sensitivity issues – ‘Pending’ vs ‘pending’

The Effective Solution: ENUM Types

PostgreSQL ENUM types provide a named set of allowed values with type-level enforcement:

-- Define the ENUM type
CREATE TYPE order_status AS ENUM (
    'pending',
    'processing', 
    'shipped',
    'delivered'
);

-- Use it like any built-in type
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    status order_status NOT NULL DEFAULT 'pending',
    total NUMERIC(10, 2) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Now PostgreSQL enforces the values:

-- Valid status
INSERT INTO orders (id, status, total) VALUES (1, 'pending', 99.99);
-- ✓ Success

-- Invalid status
INSERT INTO orders (id, status, total) VALUES (2, 'invalid', 149.99);
-- ✗ ERROR: invalid input value for enum order_status: "invalid"

-- Typo
INSERT INTO orders (id, status, total) VALUES (3, 'shippd', 199.99);
-- ✗ ERROR: invalid input value for enum order_status: "shippd"

-- Wrong case
INSERT INTO orders (id, status, total) VALUES (4, 'Pending', 249.99);
-- ✗ ERROR: invalid input value for enum order_status: "Pending"

Benefits of ENUM Types

Benefit 1: Self-Documentation

-- List all possible values
SELECT enum_range(NULL::order_status);
-- {pending,processing,shipped,delivered}

-- Or query the catalog
SELECT enumlabel 
FROM pg_enum 
JOIN pg_type ON pg_enum.enumtypid = pg_type.oid 
WHERE pg_type.typname = 'order_status'
ORDER BY enumsortorder;
-- pending
-- processing
-- shipped
-- delivered

Developers can discover valid values without reading documentation!

Benefit 2: Type Safety in Applications

Modern ORMs and type systems can generate code from ENUM definitions:

// TypeScript (auto-generated from database)
enum OrderStatus {
    Pending = 'pending',
    Processing = 'processing',
    Shipped = 'shipped',
    Delivered = 'delivered'
}

// Compile-time type safety!
order.status = OrderStatus.Shipped;  // ✓ Valid
order.status = 'invalid';  // ✗ Compile error!
order.status = 'shippd';  // ✗ Compile error!

# Python with SQLAlchemy
from enum import Enum

class OrderStatus(str, Enum):
    PENDING = 'pending'
    PROCESSING = 'processing'
    SHIPPED = 'shipped'
    DELIVERED = 'delivered'

# IDE autocomplete + static analysis
order.status = OrderStatus.SHIPPED  # ✓ Valid
order.status = 'invalid'  # IDE warning!

Benefit 3: Storage Efficiency

ENUMs are stored as 4-byte integers internally:

-- Compare storage
SELECT 
    pg_column_size('processing'::TEXT) as text_size,      -- 14 bytes
    pg_column_size('processing'::order_status) as enum_size;  -- 4 bytes

-- For 1 million rows:
-- TEXT: 14MB (10 chars + 4 byte header)
-- ENUM: 4MB (just integer)
-- Savings: 10MB per million rows!

Benefit 4: Comparison Ordering

ENUMs have a defined sort order (declaration order):

-- Order by status (uses declaration order)
SELECT id, status FROM orders ORDER BY status;
-- All 'pending' orders first
-- Then 'processing'
-- Then 'shipped'
-- Then 'delivered'

-- Comparison operators work
SELECT 'processing'::order_status > 'pending'::order_status;  -- true
SELECT 'shipped'::order_status < 'delivered'::order_status;   -- true

This is useful for workflow ordering!

Benefit 5: No Typos Possible

Database guarantees only valid values:

-- Every possible typo is caught
INSERT INTO orders (id, status, total) VALUES (1, 'pnding', 99.99);     -- ✗ ERROR
INSERT INTO orders (id, status, total) VALUES (2, 'proccessing', 99.99); -- ✗ ERROR  
INSERT INTO orders (id, status, total) VALUES (3, 'shiped', 99.99);      -- ✗ ERROR
INSERT INTO orders (id, status, total) VALUES (4, 'deliverd', 99.99);    -- ✗ ERROR

Real-World Examples

Example 1: User Roles

CREATE TYPE user_role AS ENUM (
    'guest',
    'member',
    'moderator',
    'admin',
    'superadmin'
);

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    username TEXT UNIQUE NOT NULL,
    email TEXT UNIQUE NOT NULL,
    role user_role NOT NULL DEFAULT 'member',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Test:

-- Valid roles
INSERT INTO users (id, username, email, role) 
VALUES (1, 'alice', '[email protected]', 'admin');
-- ✓ Success

INSERT INTO users (id, username, email) 
VALUES (2, 'bob', '[email protected]');
-- ✓ Success (uses default 'member')

-- Invalid role
INSERT INTO users (id, username, email, role) 
VALUES (3, 'charlie', '[email protected]', 'superuser');
-- ✗ ERROR: invalid input value for enum user_role: "superuser"

-- Query by role with ordering
SELECT username, role FROM users ORDER BY role;
-- alice    | admin       (highest role first based on enum order)
-- bob      | member

Permission checks:

-- Find all users with elevated privileges
SELECT username FROM users WHERE role >= 'moderator'::user_role;
-- alice (admin >= moderator)
-- Any moderators

Example 2: Priority Levels

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

CREATE TABLE tasks (
    id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    title TEXT NOT NULL,
    description TEXT,
    priority priority_level NOT NULL DEFAULT 'medium',
    status TEXT NOT NULL DEFAULT 'open',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Test:

-- Create tasks with priorities
INSERT INTO tasks (title, priority) VALUES 
    ('Fix typo', 'low'),
    ('Update docs', 'medium'),
    ('Production bug', 'urgent');

-- Sort by priority (critical first, then urgent, high, medium, low)
SELECT title, priority FROM tasks ORDER BY priority DESC;
-- Production bug | urgent
-- Update docs    | medium
-- Fix typo       | low

-- Filter high-priority tasks
SELECT title FROM tasks WHERE priority >= 'high'::priority_level;
-- Production bug (urgent >= high)

Example 3: Payment Methods

CREATE TYPE payment_method AS ENUM (
    'credit_card',
    'debit_card',
    'paypal',
    'bank_transfer',
    'cryptocurrency',
    'cash'
);

CREATE TABLE transactions (
    id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    order_id INTEGER NOT NULL,
    amount NUMERIC(10, 2) NOT NULL,
    method payment_method NOT NULL,
    status TEXT NOT NULL DEFAULT 'pending',
    processed_at TIMESTAMPTZ
);

Test:

-- Valid payment methods
INSERT INTO transactions (order_id, amount, method) VALUES 
    (1001, 99.99, 'credit_card'),
    (1002, 149.99, 'paypal'),
    (1003, 299.99, 'cryptocurrency');

-- Invalid payment method
INSERT INTO transactions (order_id, amount, method) VALUES 
    (1004, 199.99, 'venmo');
-- ✗ ERROR: invalid input value for enum payment_method: "venmo"

-- Analytics: Group by payment method
SELECT method, COUNT(*), SUM(amount) 
FROM transactions 
GROUP BY method 
ORDER BY method;
-- bank_transfer    | 5   | 2500.00
-- cash            | 2   | 150.00
-- credit_card     | 150 | 45000.00
-- cryptocurrency  | 3   | 1500.00
-- debit_card      | 80  | 12000.00
-- paypal          | 45  | 8900.00

Example 4: Content Visibility

CREATE TYPE visibility_level AS ENUM (
    'private',
    'friends',
    'followers',
    'public'
);

CREATE TABLE posts (
    id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    user_id INTEGER NOT NULL,
    content TEXT NOT NULL,
    visibility visibility_level NOT NULL DEFAULT 'public',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Test:

-- Create posts with different visibility
INSERT INTO posts (user_id, content, visibility) VALUES 
    (1, 'Private thoughts', 'private'),
    (1, 'Sharing with friends', 'friends'),
    (1, 'Public announcement', 'public');

-- Query based on visibility
SELECT content, visibility FROM posts 
WHERE user_id = 1 AND visibility >= 'followers'::visibility_level;
-- Sharing with friends | friends (friends >= followers)
-- Public announcement  | public (public >= followers)

Example 5: Subscription Tiers

CREATE TYPE subscription_tier AS ENUM (
    'free',
    'basic',
    'premium',
    'enterprise'
);

CREATE TABLE subscriptions (
    id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    user_id INTEGER UNIQUE NOT NULL,
    tier subscription_tier NOT NULL DEFAULT 'free',
    started_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    expires_at TIMESTAMPTZ
);

Test:

-- Create subscriptions
INSERT INTO subscriptions (user_id, tier) VALUES 
    (1, 'free'),
    (2, 'premium'),
    (3, 'enterprise');

-- Feature gating based on tier
SELECT user_id FROM subscriptions 
WHERE tier >= 'premium'::subscription_tier;
-- 2 (premium)
-- 3 (enterprise)

-- Upgrade user
UPDATE subscriptions 
SET tier = 'premium', expires_at = NOW() + INTERVAL '1 year'
WHERE user_id = 1;

Example 6: Weekdays

CREATE TYPE weekday AS ENUM (
    'monday',
    'tuesday',
    'wednesday',
    'thursday',
    'friday',
    'saturday',
    'sunday'
);

CREATE TABLE business_hours (
    id INTEGER PRIMARY KEY,
    day weekday NOT NULL,
    open_time TIME NOT NULL,
    close_time TIME NOT NULL,
    UNIQUE (day)
);

Test:

-- Set business hours
INSERT INTO business_hours (id, day, open_time, close_time) VALUES 
    (1, 'monday', '09:00', '17:00'),
    (2, 'tuesday', '09:00', '17:00'),
    (3, 'wednesday', '09:00', '17:00'),
    (4, 'thursday', '09:00', '17:00'),
    (5, 'friday', '09:00', '17:00'),
    (6, 'saturday', '10:00', '14:00');
-- Sunday not included (closed)

-- Invalid day
INSERT INTO business_hours (id, day, open_time, close_time) VALUES 
    (7, 'monday', '18:00', '22:00');
-- ✗ ERROR: duplicate key value violates unique constraint

-- Query in order
SELECT day, open_time, close_time FROM business_hours ORDER BY day;
-- monday    | 09:00 | 17:00
-- tuesday   | 09:00 | 17:00
-- ...

Advanced Patterns

Pattern 1: Multiple ENUMs in One Table

CREATE TYPE order_status AS ENUM ('pending', 'processing', 'shipped', 'delivered', 'cancelled');
CREATE TYPE payment_status AS ENUM ('unpaid', 'paid', 'refunded');
CREATE TYPE shipping_method AS ENUM ('standard', 'express', 'overnight');

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    order_status order_status NOT NULL DEFAULT 'pending',
    payment_status payment_status NOT NULL DEFAULT 'unpaid',
    shipping_method shipping_method NOT NULL DEFAULT 'standard',
    total NUMERIC(10, 2) NOT NULL
);

Test:

-- All ENUMs validated independently
INSERT INTO orders (id, order_status, payment_status, shipping_method, total) 
VALUES (1, 'processing', 'paid', 'express', 99.99);
-- ✓ Success

-- Invalid order_status
INSERT INTO orders (id, order_status, payment_status, shipping_method, total) 
VALUES (2, 'invalid', 'paid', 'express', 99.99);
-- ✗ ERROR: invalid input value for enum order_status

-- Invalid payment_status  
INSERT INTO orders (id, order_status, payment_status, shipping_method, total) 
VALUES (3, 'pending', 'partial', 'express', 99.99);
-- ✗ ERROR: invalid input value for enum payment_status

Pattern 2: ENUMs with State Transitions

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

CREATE TABLE support_tickets (
    id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    title TEXT NOT NULL,
    status ticket_status NOT NULL DEFAULT 'open',
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Function to validate state transitions
CREATE FUNCTION validate_ticket_transition() RETURNS TRIGGER AS $$
BEGIN
    -- Can't reopen closed tickets
    IF OLD.status = 'closed' AND NEW.status != 'closed' THEN
        RAISE EXCEPTION 'Cannot reopen closed ticket';
    END IF;
    
    -- Can't skip from open to resolved
    IF OLD.status = 'open' AND NEW.status = 'resolved' THEN
        RAISE EXCEPTION 'Ticket must be assigned before resolving';
    END IF;
    
    -- Update timestamp
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

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

Test:

INSERT INTO support_tickets (title, status) VALUES ('Bug report', 'open');

-- Valid transition: open → assigned
UPDATE support_tickets SET status = 'assigned' WHERE id = 1;
-- ✓ Success

-- Invalid: open → resolved (skips assignment)
UPDATE support_tickets SET status = 'resolved' WHERE id = 1;
-- ✗ ERROR: Ticket must be assigned before resolving

-- Valid path: assigned → in_progress → resolved → closed
UPDATE support_tickets SET status = 'in_progress' WHERE id = 1;
UPDATE support_tickets SET status = 'resolved' WHERE id = 1;
UPDATE support_tickets SET status = 'closed' WHERE id = 1;
-- ✓ All succeed

-- Invalid: Try to reopen closed ticket
UPDATE support_tickets SET status = 'open' WHERE id = 1;
-- ✗ ERROR: Cannot reopen closed ticket

Pattern 3: ENUMs in Arrays

CREATE TYPE tag AS ENUM ('bug', 'feature', 'documentation', 'performance', 'security');

CREATE TABLE issues (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    tags tag[] NOT NULL DEFAULT '{}'
);

Test:

-- Valid tags array
INSERT INTO issues (id, title, tags) VALUES 
    (1, 'Fix login bug', ARRAY['bug', 'security']::tag[]);
-- ✓ Success

-- Invalid tag in array
INSERT INTO issues (id, title, tags) VALUES 
    (2, 'Add feature', ARRAY['feature', 'enhancement']::tag[]);
-- ✗ ERROR: invalid input value for enum tag: "enhancement"

-- Query issues with specific tag
SELECT title FROM issues WHERE 'security'::tag = ANY(tags);
-- Fix login bug

Pattern 4: ENUMs in Composite Types

CREATE TYPE priority_level AS ENUM ('low', 'medium', 'high', 'critical');
CREATE TYPE task_status AS ENUM ('todo', 'in_progress', 'done');

CREATE TYPE task_metadata AS (
    priority priority_level,
    status task_status,
    estimated_hours INTEGER
);

CREATE TABLE projects (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    task_info task_metadata
);

Test:

-- Valid composite with ENUMs
INSERT INTO projects (id, name, task_info) VALUES 
    (1, 'Website Redesign', ROW('high', 'in_progress', 40)::task_metadata);
-- ✓ Success

-- Invalid ENUM in composite
INSERT INTO projects (id, name, task_info) VALUES 
    (2, 'Mobile App', ROW('urgent', 'todo', 80)::task_metadata);
-- ✗ ERROR: invalid input value for enum priority_level: "urgent"

Modifying ENUM Types

Adding Values to ENUMs

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

-- Add new value at the end
ALTER TYPE order_status ADD VALUE 'cancelled';

-- Verify
SELECT enum_range(NULL::order_status);
-- {pending,processing,shipped,delivered,cancelled}

Add value at specific position:

-- Add 'confirmed' between 'pending' and 'processing'
ALTER TYPE order_status ADD VALUE 'confirmed' BEFORE 'processing';

-- Add 'returned' after 'delivered'
ALTER TYPE order_status ADD VALUE 'returned' AFTER 'delivered';

SELECT enum_range(NULL::order_status);
-- {pending,confirmed,processing,shipped,delivered,returned,cancelled}

Important Limitations

⚠️ Cannot add values inside a transaction:

BEGIN;
ALTER TYPE order_status ADD VALUE 'on_hold';
-- ✗ ERROR: ALTER TYPE ... ADD VALUE cannot run inside a transaction block
COMMIT;

⚠️ Cannot remove values:

-- NO WAY TO DO THIS!
ALTER TYPE order_status REMOVE VALUE 'cancelled';
-- ✗ ERROR: syntax error

-- Workaround: Create new type, migrate data, drop old type

⚠️ Cannot rename values:

-- NO WAY TO DO THIS!
ALTER TYPE order_status RENAME VALUE 'cancelled' TO 'canceled';
-- ✗ ERROR: syntax error

-- Workaround: Create new type with corrected spelling

Renaming ENUMs

-- Rename the type itself
ALTER TYPE order_status RENAME TO order_state;

-- Update references in your code
-- Type rename is automatic in database, but application code must update

Migrating from TEXT to ENUM

Step-by-Step Migration

-- Step 1: Current table with TEXT
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    status TEXT NOT NULL,
    total NUMERIC(10, 2)
);

-- Step 2: Analyze existing data
SELECT DISTINCT status FROM orders ORDER BY status;
-- cancelled
-- delivered
-- invalid    ← Need to clean this up!
-- pending
-- processing
-- shipped

-- Step 3: Clean invalid data
UPDATE orders SET status = 'cancelled' WHERE status = 'invalid';

-- Step 4: Create ENUM with all valid values
CREATE TYPE order_status AS ENUM (
    'pending',
    'processing',
    'shipped',
    'delivered',
    'cancelled'
);

-- Step 5: Add new column with ENUM type
ALTER TABLE orders ADD COLUMN status_enum order_status;

-- Step 6: Populate new column
UPDATE orders SET status_enum = status::order_status;

-- Step 7: Make new column NOT NULL
ALTER TABLE orders ALTER COLUMN status_enum SET NOT NULL;

-- Step 8: Drop old column
ALTER TABLE orders DROP COLUMN status;

-- Step 9: Rename new column
ALTER TABLE orders RENAME COLUMN status_enum TO status;

-- Step 10: Update default
ALTER TABLE orders ALTER COLUMN status SET DEFAULT 'pending';

Verify:

\d orders
-- id      | integer       | not null
-- status  | order_status  | not null default 'pending'::order_status
-- total   | numeric(10,2) |

Querying ENUM Metadata

List All ENUMs

SELECT 
    n.nspname AS schema,
    t.typname AS enum_name,
    STRING_AGG(e.enumlabel, ', ' ORDER BY e.enumsortorder) AS values
FROM pg_type t
JOIN pg_enum e ON t.oid = e.enumtypid
JOIN pg_namespace n ON t.typnamespace = n.oid
GROUP BY n.nspname, t.typname
ORDER BY n.nspname, t.typname;

-- schema | enum_name      | values
-- -------+----------------+----------------------------------------
-- public | order_status   | pending, processing, shipped, delivered
-- public | payment_method | credit_card, debit_card, paypal
-- public | user_role      | guest, member, moderator, admin

Get Values for Specific ENUM

-- Using enum_range
SELECT unnest(enum_range(NULL::order_status)) AS status;
-- pending
-- processing
-- shipped
-- delivered

-- Using catalog query
SELECT enumlabel AS status
FROM pg_enum
JOIN pg_type ON pg_enum.enumtypid = pg_type.oid
WHERE pg_type.typname = 'order_status'
ORDER BY enumsortorder;

Find Tables Using an ENUM

SELECT 
    c.table_schema,
    c.table_name,
    c.column_name
FROM information_schema.columns c
WHERE c.udt_name = 'order_status'
ORDER BY c.table_name, c.column_name;

-- table_schema | table_name | column_name
-- -------------+------------+-------------
-- public       | orders     | status
-- public       | shipments  | order_status

Performance Considerations

Storage Efficiency

-- Compare sizes
CREATE TABLE text_statuses (
    id INTEGER PRIMARY KEY,
    status TEXT
);

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

CREATE TABLE enum_statuses (
    id INTEGER PRIMARY KEY,
    status status_enum
);

-- Insert 1 million rows
INSERT INTO text_statuses 
SELECT i, 'processing' FROM generate_series(1, 1000000) i;

INSERT INTO enum_statuses 
SELECT i, 'processing' FROM generate_series(1, 1000000) i;

-- Check sizes
SELECT 
    pg_size_pretty(pg_total_relation_size('text_statuses')) AS text_size,
    pg_size_pretty(pg_total_relation_size('enum_statuses')) AS enum_size;
    
-- text_size | enum_size
-- ----------+-----------
-- 42 MB     | 35 MB
-- Savings: ~17% for this example

Index Performance

-- ENUMs can be indexed efficiently
CREATE INDEX idx_orders_status ON orders(status);

-- Query performance is identical or better than TEXT
EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'shipped';
-- Uses index efficiently (4 bytes comparison instead of string)

Sorting Performance

-- ENUM sort is faster (integer comparison)
EXPLAIN ANALYZE SELECT * FROM orders ORDER BY status LIMIT 100;
-- Faster than TEXT sorting (no string comparison needed)

Common Pitfalls

Pitfall 1: Adding Values Requires Locks

-- Adding ENUM value requires ACCESS EXCLUSIVE lock on the type
-- This blocks ALL operations using the ENUM
ALTER TYPE order_status ADD VALUE 'refunded';
-- Locks: ALL tables with order_status columns

-- For busy systems, this can cause downtime!

Workaround for zero-downtime:

-- 1. Add value outside peak hours
-- 2. Or use TEXT with CHECK constraint if values change frequently

Pitfall 2: Can’t Use ENUMs in Transactions

BEGIN;
CREATE TYPE new_status AS ENUM ('active', 'inactive');
-- ✓ OK to create

ALTER TYPE new_status ADD VALUE 'suspended';
-- ✗ ERROR: ALTER TYPE ... ADD VALUE cannot run inside a transaction block
ROLLBACK;

Workaround:

-- Create ENUM with all expected values upfront
CREATE TYPE new_status AS ENUM ('active', 'inactive', 'suspended', 'deleted');
-- Even if you don't use all values yet

Pitfall 3: No Way to Remove Values

CREATE TYPE status AS ENUM ('active', 'inactive', 'deprecated');

-- Later: Want to remove 'deprecated'
-- NO BUILT-IN WAY TO DO THIS!

-- Only workaround: Create new type, migrate data
CREATE TYPE status_v2 AS ENUM ('active', 'inactive');

ALTER TABLE users ADD COLUMN status_new status_v2;
UPDATE users SET status_new = 
    CASE 
        WHEN status = 'deprecated' THEN 'inactive'
        ELSE status::TEXT::status_v2
    END;
ALTER TABLE users DROP COLUMN status;
ALTER TABLE users RENAME COLUMN status_new TO status;
DROP TYPE status;
ALTER TYPE status_v2 RENAME TO status;

Pitfall 4: Order Matters

-- Wrong order for priority
CREATE TYPE priority AS ENUM ('critical', 'high', 'medium', 'low');

-- This gives unexpected results!
SELECT 'low'::priority > 'critical'::priority;  -- true (because 'low' comes after 'critical')

Fix: Define in logical order:

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

-- Now comparisons work as expected
SELECT 'critical'::priority > 'low'::priority;  -- true (as expected)

Pitfall 5: Case Sensitivity

CREATE TYPE status AS ENUM ('active', 'inactive');

-- ENUMs are case-sensitive!
SELECT 'Active'::status;
-- ✗ ERROR: invalid input value for enum status: "Active"

SELECT 'ACTIVE'::status;
-- ✗ ERROR: invalid input value for enum status: "ACTIVE"

Fix: Use lowercase consistently:

CREATE TYPE status AS ENUM ('active', 'inactive');
-- Always use lowercase in application code

When NOT to Use ENUMs

Case 1: Frequently Changing Values

If values change often (weekly/monthly), ENUMs are cumbersome:

-- ❌ BAD: Product categories change frequently
CREATE TYPE product_category AS ENUM ('electronics', 'clothing', 'books');

-- Adding new category requires:
-- 1. ALTER TYPE (locks all tables)
-- 2. Application deployment
-- 3. Coordination across teams

Better: Use a lookup table:

CREATE TABLE product_categories (
    id INTEGER PRIMARY KEY,
    name TEXT UNIQUE NOT NULL
);

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    category_id INTEGER REFERENCES product_categories(id)
);

-- Adding new category is just an INSERT
INSERT INTO product_categories (id, name) VALUES (4, 'furniture');

Case 2: User-Defined Values

If users can define their own values:

-- ❌ BAD: Custom tags defined by users
CREATE TYPE user_tag AS ENUM ('important', 'urgent', 'later');
-- Users can't add their own tags!

Better: Just use TEXT or a tags table.

Case 3: Large Value Sets

If you have 100+ possible values:

-- ❌ BAD: Country codes (195+ countries)
CREATE TYPE country_code AS ENUM ('US', 'GB', 'FR', 'DE', ... 195 more);
-- Unwieldy to maintain

Better: Use TEXT with CHECK or lookup table.

Case 4: Values Need Display Names

-- ❌ LIMITED: ENUM value is also the display name
CREATE TYPE status AS ENUM ('pending', 'in_progress', 'completed');
-- Display as "In Progress"? Need mapping in application

Better: Lookup table with display names:

CREATE TABLE statuses (
    code TEXT PRIMARY KEY,
    display_name TEXT NOT NULL
);

INSERT INTO statuses VALUES 
    ('pending', 'Pending'),
    ('in_progress', 'In Progress'),
    ('completed', 'Completed');

ENUM vs Alternatives Comparison

FeatureENUMTEXT + CHECKLookup Table
Type safety✅ Strong⚠️ Weak✅ Strong (FK)
Storage efficiency✅ 4 bytes❌ Full string⚠️ FK overhead
Add values⚠️ ALTER TYPE⚠️ ALTER constraint✅ INSERT row
Remove values❌ Not possible⚠️ ALTER constraint✅ DELETE row
Discoverability✅ enum_range()❌ Read constraint✅ SELECT *
Ordering✅ Declaration order❌ Alphabetic✅ Custom column
Display names❌ Value is name❌ Value is name✅ Separate column
Transaction safety❌ Can’t add in tx✅ Yes✅ Yes
Application sync⚠️ Generate from DB❌ Hardcode✅ Query runtime
Best forFixed, small sets (3-20 values)Temporary/prototypeDynamic, large sets

Summary

Key Takeaways:

  1. Use ENUMs for fixed, small sets of values (typically 3-20)
  2. Define values in logical order (affects comparison operators)
  3. Use lowercase for consistency
  4. Create with all expected values upfront (can’t add in transactions)
  5. ENUMs provide type safety, storage efficiency, and self-documentation
  6. Don’t use ENUMs for frequently changing or user-defined values
  7. Can’t remove or rename values (requires migration)
  8. Adding values requires exclusive lock (plan for low-traffic periods)

When to Use What:

  • ENUM: Status workflows, roles, priorities, payment methods (fixed sets)
  • TEXT + CHECK: Prototyping, temporary constraints
  • Lookup Table: Dynamic sets, need display names, 50+ values, frequent changes

The Bottom Line:

ENUMs are PostgreSQL’s way of creating type-safe enumerations that prevent typos, enable autocomplete, optimize storage, and self-document valid values. Use them for fixed sets of values that rarely change, and you’ll eliminate a whole class of data quality issues.

Further Reading

Next in the series: Item 6 – Design Foreign Keys with Appropriate ON DELETE/UPDATE Actions

Leave a Comment