AdvancedAdvanced PostgreSQL

PostgreSQL: enum types

The query

SQL

-- Create enum type
CREATE TYPE order_status AS ENUM ('pending', 'processing', 'shipped', 'completed', 'cancelled');
CREATE TYPE emp_status AS ENUM ('active', 'inactive', 'on_leave', 'terminated');

-- Add to table
ALTER TABLE orders ALTER COLUMN status TYPE order_status USING status::order_status;

-- Query enum values
SELECT unnest(enum_range(NULL::order_status)) AS status_values;

-- Add new value to enum (PostgreSQL 9.1+)
ALTER TYPE order_status ADD VALUE 'returned' AFTER 'delivered';

-- Enum comparison works as expected
SELECT * FROM orders WHERE status > 'processing'; -- returns shipped, completed, cancelled

Tested against PostgreSQL 16

Note

“Enum types enforce valid values at DB level. Ordering is based on enum definition order. Safer than VARCHAR.

Tables referenced

orders6 cols