PostgreSQL Deep Dive Part 4 — Mastering Enums and Domains

-- First, let's define our order states
CREATE TYPE order_status AS ENUM (
    'pending',
    'processing',
    'shipped',
    'delivered',
    'cancelled',
    'returned'
);
 
-- Create our orders table using the enum
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    status order_status NOT NULL DEFAULT 'pending',
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);
 
INSERT INTO orders (status) VALUES
	('pending'),
	('processing');
-- Throws error if status values does not match with ENUM values

-- Ordering of table will happen based on enum position
 
INSERT INTO orders (status) VALUES
('pending'),
('processing'),
('shipped'),
('delivered'),
('cancelled'),
('returned'),
('pending'),
('processing'),
('shipped'),
('cancelled');
 
SELECT
    order_id,
    status,
FROM orders
ORDER BY status;
 
--| order_id | status     |
--|----------|------------|
--|        9 | pending    |
--|        1 | pending    |
--|        3 | pending    |
--|       10 | processing |
--|        4 | processing |
--|        2 | processing |
--|       11 | shipped    |
--|        5 | shipped    |
--|        6 | delivered  |
--|       12 | cancelled  |
--|        7 | cancelled  |
--|        8 | returned   |
 
-- Here ordering is not alphabetical order but on the order of ENUM defined

-- Add a new value at the end of enum list
ALTER TYPE order_status ADD VALUE 'payment_pending';
 
-- Add values at specific positions in ENUM
ALTER TYPE order_status ADD VALUE 'payment_done' BEFORE 'processing';
ALTER TYPE order_status ADD VALUE 'processing_done' AFTER 'processing';
 
-- View all enum values
SELECT * FROM pg_catalog.pg_enum;
 
-- Display enum ranges
SELECT enum_range(null::mood);                    -- Show all enum values
SELECT enum_range(null::mood, 'sad'::mood);       -- Show values from start to 'sad'
SELECT enum_range('afraid'::mood, 'sad'::mood);   -- Show values from 'afraid' to 'sad'

-- Create a comprehensive email domain
CREATE DOMAIN email_address AS TEXT
CHECK (
    VALUE ~ '^[a-zA-Z0-9.!#$%&''*+/=?^_`{|}~-]+@[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?(?:\.[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?)*$'
    AND length(VALUE) <= 254
);
 
-- Create a US phone number domain
CREATE DOMAIN us_phone AS TEXT
CHECK (
    VALUE ~ '^\+1[0-9]{10}$'
    OR VALUE ~ '^[0-9]{10}$'
);
 
CREATE DOMAIN us_postal_code AS TEXT
CONSTRAINT format CHECK (
    VALUE ~ '^\d{5}$' OR VALUE ~ '^\d{5}-\d{4}$'
);
 
-- Practical application
CREATE TABLE contacts (
    id SERIAL PRIMARY KEY,
    email email_address NOT NULL,
    phone us_phone,
    postal us_postal_code NOT NULL,
    verified BOOLEAN DEFAULT FALSE
);

-- Testing domain constraints with examples
INSERT INTO contacts (email, phone, postal) VALUES
    ('valid@email.com', '+11234567890', '12345'),    -- Works
    ('invalid@', '+11234567890', '12345'),           -- Fails email check
    ('valid@email.com', '123-456-7890', '12345'),    -- Fails phone check
    ('valid@email.com', '+11234567890', '123456');   -- Fails postal check
 
-- Creating a domain for monetary amounts with business rules
CREATE DOMAIN positive_amount AS NUMERIC
CHECK (
    VALUE > 0                    -- Must be positive
    AND VALUE < 1000000         -- Less than 1 million
    AND scale(VALUE) <= 2       -- Max 2 decimal places
);
 
-- Using the domain in a financial transactions table
CREATE TABLE transactions (
    id SERIAL PRIMARY KEY,
    amount positive_amount,      -- Uses our custom domain
    description TEXT
);