Item 4 – Prefer DOMAIN Types for Reusable Validation

by

The Problem: Repeating Validation Logic Everywhere

You’re building a system with email addresses scattered across multiple tables:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL,
    CHECK (email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')
);

CREATE TABLE contacts (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL,
    CHECK (email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')
);

CREATE TABLE subscribers (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL,
    CHECK (email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')
);

CREATE TABLE support_tickets (
    id INTEGER PRIMARY KEY,
    requester_email TEXT NOT NULL,
    cc_emails TEXT[],
    CHECK (requester_email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')
    -- What about cc_emails array? Can't easily validate each element!
);

Problems with this approach:

Problem 1: Copy-Paste Error Prone

One typo in the regex and you have inconsistent validation:

-- Oops, developer makes a typo in one table
CREATE TABLE newsletter_signups (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL,
    CHECK (email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')  -- Correct
);

CREATE TABLE event_registrations (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL,
    CHECK (email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,$')   -- TYPO: Missing }
);
-- Creates invalid constraint!

Problem 2: Difficult to Update Validation Rules

Business decides to allow longer TLDs (.museum, .international):

-- Now you must update 10+ tables individually
ALTER TABLE users DROP CONSTRAINT users_email_check;
ALTER TABLE users ADD CONSTRAINT users_email_check 
    CHECK (email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

ALTER TABLE contacts DROP CONSTRAINT contacts_email_check;
ALTER TABLE contacts ADD CONSTRAINT contacts_email_check 
    CHECK (email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

-- ... repeat for every table
-- Miss one table and you have inconsistent validation

Problem 3: No Self-Documentation

Looking at a table schema, you can’t easily tell what validation rules apply:

\d users
-- Shows: email TEXT with some CHECK constraint
-- Developer must read the constraint code to understand the rules

Problem 4: Can’t Validate Array Elements or Function Parameters

-- How do you validate each email in an array?
CREATE TABLE group_invites (
    id INTEGER PRIMARY KEY,
    recipient_emails TEXT[]
    -- Can't add CHECK constraint for array elements!
);

-- How do you validate function parameters?
CREATE FUNCTION send_email(recipient TEXT) RETURNS void AS $$
    -- No way to enforce email validation at the function signature level
$$ LANGUAGE plpgsql;

The Effective Solution: DOMAIN Types

A DOMAIN is a user-defined data type with built-in constraints. Define validation once, reuse everywhere:

-- Define the domain once
CREATE DOMAIN email_address AS TEXT
CHECK (
    VALUE ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'
);

-- Now use it like any built-in type
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email email_address NOT NULL  -- Validation automatically applied!
);

CREATE TABLE contacts (
    id INTEGER PRIMARY KEY,
    email email_address NOT NULL  -- Same validation, no copy-paste
);

CREATE TABLE subscribers (
    id INTEGER PRIMARY KEY,
    email email_address NOT NULL  -- Consistent across all tables
);

Test it:

-- Valid email
INSERT INTO users (id, email) VALUES (1, '[email protected]');
-- ✓ Success

-- Invalid email
INSERT INTO users (id, email) VALUES (2, 'not-an-email');
-- ✗ ERROR: value for domain email_address violates check constraint "email_address_check"

-- Invalid email in different table - same error!
INSERT INTO contacts (id, email) VALUES (1, 'invalid@');
-- ✗ ERROR: value for domain email_address violates check constraint "email_address_check"

Real-World Examples

Example 1: Email Addresses (Complete Implementation)

-- Define email domain with case-insensitive storage
CREATE EXTENSION IF NOT EXISTS citext;

CREATE DOMAIN email_address AS citext
CHECK (
    VALUE ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'
);

-- Add documentation
COMMENT ON DOMAIN email_address IS 
'Valid email address format. Case-insensitive storage. Regex validates basic structure.';

Use in multiple contexts:

-- Regular columns
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email email_address UNIQUE NOT NULL
);

-- Arrays (validation applies to each element!)
CREATE TABLE group_messages (
    id INTEGER PRIMARY KEY,
    recipients email_address[] NOT NULL
);

-- Function parameters (type safety!)
CREATE FUNCTION send_notification(recipient email_address) RETURNS void AS $$
BEGIN
    RAISE NOTICE 'Sending email to: %', recipient;
END;
$$ LANGUAGE plpgsql;

-- Composite types
CREATE TYPE contact_info AS (
    primary_email email_address,
    secondary_email email_address,
    phone TEXT
);

Test array validation:

-- Valid array of emails
INSERT INTO group_messages (id, recipients) VALUES 
    (1, ARRAY['[email protected]', '[email protected]']::email_address[]);
-- ✓ Success

-- Invalid email in array
INSERT INTO group_messages (id, recipients) VALUES 
    (2, ARRAY['[email protected]', 'not-valid']::email_address[]);
-- ✗ ERROR: value for domain email_address violates check constraint
-- (Catches the invalid element!)

Test function parameter validation:

-- Valid call
SELECT send_notification('[email protected]');
-- ✓ Success: NOTICE: Sending email to: [email protected]

-- Invalid call
SELECT send_notification('not-an-email');
-- ✗ ERROR: value for domain email_address violates check constraint
-- (Validation happens before function executes!)

Example 2: Postal Codes (Country-Specific)

-- US ZIP codes (5 digits or 5+4 format)
CREATE DOMAIN us_zip_code AS TEXT
CHECK (VALUE ~ '^\d{5}(-\d{4})?$');

-- Canadian postal codes (A1A 1A1 format)
CREATE DOMAIN canadian_postal_code AS TEXT
CHECK (VALUE ~ '^[A-Z]\d[A-Z] \d[A-Z]\d$');

-- UK postcodes (complex format)
CREATE DOMAIN uk_postcode AS TEXT
CHECK (VALUE ~ '^[A-Z]{1,2}\d{1,2}[A-Z]? \d[A-Z]{2}$');

COMMENT ON DOMAIN us_zip_code IS 'US ZIP code: 12345 or 12345-6789';
COMMENT ON DOMAIN canadian_postal_code IS 'Canadian postal code: A1A 1A1';
COMMENT ON DOMAIN uk_postcode IS 'UK postcode: SW1A 1AA';

Usage:

CREATE TABLE us_addresses (
    id INTEGER PRIMARY KEY,
    street TEXT NOT NULL,
    city TEXT NOT NULL,
    state CHAR(2) NOT NULL,
    zip_code us_zip_code NOT NULL
);

CREATE TABLE canadian_addresses (
    id INTEGER PRIMARY KEY,
    street TEXT NOT NULL,
    city TEXT NOT NULL,
    province CHAR(2) NOT NULL,
    postal_code canadian_postal_code NOT NULL
);

Test:

-- Valid US ZIP
INSERT INTO us_addresses VALUES (1, '123 Main St', 'New York', 'NY', '10001');
INSERT INTO us_addresses VALUES (2, '456 Oak Ave', 'Los Angeles', 'CA', '90210-1234');
-- ✓ Both succeed

-- Invalid US ZIP
INSERT INTO us_addresses VALUES (3, '789 Elm St', 'Chicago', 'IL', '606');
-- ✗ ERROR: value for domain us_zip_code violates check constraint

-- Valid Canadian postal code
INSERT INTO canadian_addresses VALUES (1, '100 Main St', 'Toronto', 'ON', 'M5H 2N2');
-- ✓ Success

-- Invalid Canadian postal code (lowercase)
INSERT INTO canadian_addresses VALUES (2, '200 King St', 'Vancouver', 'BC', 'm5h 2n2');
-- ✗ ERROR: value for domain canadian_postal_code violates check constraint

Example 3: Monetary Values

-- Money must be non-negative with 2 decimal places
CREATE DOMAIN money_amount AS NUMERIC(12, 2)
CHECK (VALUE >= 0);

-- Percentage (0-100)
CREATE DOMAIN percentage AS NUMERIC(5, 2)
CHECK (VALUE >= 0 AND VALUE <= 100);

-- Discount percentage (0-100, but typically smaller)
CREATE DOMAIN discount_percentage AS NUMERIC(5, 2)
CHECK (VALUE >= 0 AND VALUE <= 100);

COMMENT ON DOMAIN money_amount IS 'Non-negative monetary value with 2 decimal places';
COMMENT ON DOMAIN percentage IS 'Percentage value between 0 and 100';

Usage:

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price money_amount NOT NULL,
    discount discount_percentage DEFAULT 0
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    subtotal money_amount NOT NULL,
    tax_rate percentage NOT NULL,
    total money_amount NOT NULL
);

Test:

-- Valid product
INSERT INTO products VALUES (1, 'Widget', 19.99, 10);
-- ✓ Success

-- Invalid: Negative price
INSERT INTO products VALUES (2, 'Gadget', -5.00, 0);
-- ✗ ERROR: value for domain money_amount violates check constraint

-- Invalid: Discount over 100%
INSERT INTO products VALUES (3, 'Premium', 99.99, 150);
-- ✗ ERROR: value for domain discount_percentage violates check constraint

-- Invalid: Too many decimal places
INSERT INTO products VALUES (4, 'Deluxe', 49.999, 0);
-- ✗ ERROR: numeric field overflow (automatically enforced by NUMERIC(12,2))

Example 4: Phone Numbers

-- US phone numbers (various formats normalized to digits-only)
CREATE DOMAIN us_phone AS TEXT
CHECK (VALUE ~ '^\d{10}$');

-- International phone numbers (E.164 format)
CREATE DOMAIN international_phone AS TEXT
CHECK (VALUE ~ '^\+[1-9]\d{1,14}$');

COMMENT ON DOMAIN us_phone IS 'US phone number: 10 digits only (no formatting)';
COMMENT ON DOMAIN international_phone IS 'E.164 format: +[country][number]';

Usage with normalization function:

-- Function to normalize US phone input
CREATE FUNCTION normalize_us_phone(input TEXT) RETURNS us_phone AS $$
    SELECT regexp_replace(input, '[^0-9]', '', 'g')::us_phone;
$$ LANGUAGE SQL IMMUTABLE;

CREATE TABLE contacts (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    phone us_phone NOT NULL
);

Test:

-- Use normalization function for user input
INSERT INTO contacts VALUES (1, 'Alice', normalize_us_phone('(555) 123-4567'));
-- Stored as: '5551234567' ✓

INSERT INTO contacts VALUES (2, 'Bob', normalize_us_phone('555.123.4567'));
-- Stored as: '5551234567' ✓

-- Direct insert must be normalized
INSERT INTO contacts VALUES (3, 'Charlie', '5551234567');
-- ✓ Success

-- Invalid format
INSERT INTO contacts VALUES (4, 'Dave', '555-1234');
-- ✗ ERROR: value for domain us_phone violates check constraint (not 10 digits)

Example 5: URLs and Slugs

-- URL slug (lowercase, alphanumeric with hyphens)
CREATE DOMAIN slug AS TEXT
CHECK (VALUE ~ '^[a-z0-9]+(-[a-z0-9]+)*$' AND LENGTH(VALUE) <= 100);

-- HTTP/HTTPS URL
CREATE DOMAIN web_url AS TEXT
CHECK (VALUE ~ '^https?://[^\s]+$' AND LENGTH(VALUE) <= 2048);

COMMENT ON DOMAIN slug IS 'URL-safe slug: lowercase alphanumeric with hyphens, max 100 chars';
COMMENT ON DOMAIN web_url IS 'HTTP or HTTPS URL, max 2048 chars';

Usage:

CREATE TABLE articles (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    slug slug UNIQUE NOT NULL,
    content TEXT NOT NULL
);

CREATE TABLE bookmarks (
    id INTEGER PRIMARY KEY,
    url web_url NOT NULL,
    title TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Test:

-- Valid slug
INSERT INTO articles VALUES (1, 'Getting Started', 'getting-started', 'Content...');
-- ✓ Success

-- Invalid: uppercase
INSERT INTO articles VALUES (2, 'Advanced Topics', 'Advanced-Topics', 'Content...');
-- ✗ ERROR: value for domain slug violates check constraint

-- Invalid: special characters
INSERT INTO articles VALUES (3, 'Tips & Tricks', 'tips-&-tricks', 'Content...');
-- ✗ ERROR: value for domain slug violates check constraint

-- Valid URL
INSERT INTO bookmarks VALUES (1, 'https://example.com/page', 'Example');
-- ✓ Success

-- Invalid: No protocol
INSERT INTO bookmarks VALUES (2, 'example.com', 'Example');
-- ✗ ERROR: value for domain web_url violates check constraint

Example 6: Age and Date Ranges

-- Age must be reasonable (0-150)
CREATE DOMAIN age_years AS INTEGER
CHECK (VALUE >= 0 AND VALUE <= 150);

-- Year (4 digits, reasonable range)
CREATE DOMAIN calendar_year AS INTEGER
CHECK (VALUE >= 1900 AND VALUE <= 2100);

-- Rating (1-5 stars)
CREATE DOMAIN star_rating AS INTEGER
CHECK (VALUE >= 1 AND VALUE <= 5);

COMMENT ON DOMAIN age_years IS 'Age in years: 0-150';
COMMENT ON DOMAIN calendar_year IS 'Calendar year: 1900-2100';
COMMENT ON DOMAIN star_rating IS 'Star rating: 1-5';

Usage:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    birth_year calendar_year NOT NULL,
    age age_years GENERATED ALWAYS AS (EXTRACT(YEAR FROM CURRENT_DATE) - birth_year) STORED
);

CREATE TABLE reviews (
    id INTEGER PRIMARY KEY,
    product_id INTEGER NOT NULL,
    rating star_rating NOT NULL,
    comment TEXT
);

Test:

-- Valid user
INSERT INTO users (id, name, birth_year) VALUES (1, 'Alice', 1990);
-- ✓ Success, age automatically calculated

-- Invalid: Birth year in the future
INSERT INTO users (id, name, birth_year) VALUES (2, 'Bob', 2150);
-- ✗ ERROR: value for domain calendar_year violates check constraint

-- Valid review
INSERT INTO reviews VALUES (1, 100, 5, 'Excellent!');
-- ✓ Success

-- Invalid: Rating out of range
INSERT INTO reviews VALUES (2, 100, 6, 'Beyond excellent!');
-- ✗ ERROR: value for domain star_rating violates check constraint

Advanced Patterns

Pattern 1: Domains with Multiple Constraints

-- Password hash: must be bcrypt format
CREATE DOMAIN password_hash AS TEXT
CHECK (VALUE ~ '^\$2[aby]\$\d{2}\$.{53}$')  -- Bcrypt format
CHECK (LENGTH(VALUE) = 60);                  -- Exact length

-- Social Security Number (US): XXX-XX-XXXX format
CREATE DOMAIN ssn AS TEXT
CHECK (VALUE ~ '^\d{3}-\d{2}-\d{4}$')
CHECK (VALUE != '000-00-0000')               -- Invalid test SSN
CHECK (VALUE !~ '^666-')                     -- Invalid area number
CHECK (VALUE !~ '^9\d{2}-');                 -- Reserved range

COMMENT ON DOMAIN ssn IS 'US Social Security Number: XXX-XX-XXXX format with validation';

Test:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    username TEXT UNIQUE NOT NULL,
    password_hash password_hash NOT NULL
);

-- Valid bcrypt hash
INSERT INTO users VALUES (1, 'alice', '$2b$12$abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUV');
-- ✓ Success

-- Invalid: Wrong format
INSERT INTO users VALUES (2, 'bob', 'plaintext_password');
-- ✗ ERROR: value for domain password_hash violates check constraint

-- Invalid: Wrong length
INSERT INTO users VALUES (3, 'charlie', '$2b$12$short');
-- ✗ ERROR: value for domain password_hash violates check constraint

Pattern 2: Domains Based on Other Domains

-- Base domain: non-empty text
CREATE DOMAIN non_empty_text AS TEXT
CHECK (LENGTH(TRIM(VALUE)) > 0);

-- Specific domains built on base
CREATE DOMAIN username AS non_empty_text
CHECK (LENGTH(VALUE) <= 50)
CHECK (VALUE ~ '^[a-zA-Z0-9_]+$');

CREATE DOMAIN product_name AS non_empty_text
CHECK (LENGTH(VALUE) <= 200);

COMMENT ON DOMAIN non_empty_text IS 'Text that is not empty or whitespace-only';
COMMENT ON DOMAIN username IS 'Username: 1-50 chars, alphanumeric and underscore only';
COMMENT ON DOMAIN product_name IS 'Product name: 1-200 chars, no leading/trailing whitespace';

Note: Constraints from base domain are inherited!

-- Test username
SELECT ''::username;
-- ✗ ERROR: Fails non_empty_text check

SELECT '   '::username;
-- ✗ ERROR: Fails non_empty_text check (whitespace-only)

SELECT 'alice@123'::username;
-- ✗ ERROR: Fails username-specific check (@ not allowed)

SELECT 'alice_123'::username;
-- ✓ Success

Pattern 3: Domains with DEFAULT Values

-- Status with default
CREATE DOMAIN order_status AS TEXT
DEFAULT 'pending'
CHECK (VALUE IN ('pending', 'processing', 'shipped', 'delivered', 'cancelled'));

-- Priority with default
CREATE DOMAIN task_priority AS INTEGER
DEFAULT 5
CHECK (VALUE >= 1 AND VALUE <= 10);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    status order_status  -- Defaults to 'pending'
);

CREATE TABLE tasks (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    priority task_priority  -- Defaults to 5
);

Test:

-- No status specified - uses default
INSERT INTO orders (id) VALUES (1);

SELECT * FROM orders WHERE id = 1;
-- id | status
-- ---+--------
--  1 | pending

-- No priority specified - uses default
INSERT INTO tasks (id, title) VALUES (1, 'Fix bug');

SELECT * FROM tasks WHERE id = 1;
-- id | title    | priority
-- ---+----------+---------
--  1 | Fix bug  |        5

Pattern 4: Domains for Composite Types

CREATE DOMAIN email_address AS citext
CHECK (VALUE ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

CREATE DOMAIN us_phone AS TEXT
CHECK (VALUE ~ '^\d{10}$');

-- Composite type using domains
CREATE TYPE contact_details AS (
    primary_email email_address,
    secondary_email email_address,
    work_phone us_phone,
    mobile_phone us_phone
);

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    contact contact_details NOT NULL
);

Test:

-- Valid contact
INSERT INTO customers VALUES (
    1, 
    'Alice Smith', 
    ROW('[email protected]', '[email protected]', '5551234567', '5559876543')::contact_details
);
-- ✓ Success

-- Invalid: Bad email in composite
INSERT INTO customers VALUES (
    2,
    'Bob Jones',
    ROW('[email protected]', 'invalid-email', '5551234567', '5559876543')::contact_details
);
-- ✗ ERROR: value for domain email_address violates check constraint

Modifying Domain Constraints

Adding Constraints to Existing Domains

-- Create simple domain
CREATE DOMAIN email_address AS TEXT;

-- Add constraint later
ALTER DOMAIN email_address 
ADD CONSTRAINT email_format_check 
CHECK (VALUE ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

-- Add another constraint
ALTER DOMAIN email_address
ADD CONSTRAINT email_not_empty
CHECK (LENGTH(VALUE) > 0);

Dropping Constraints

-- Remove specific constraint
ALTER DOMAIN email_address 
DROP CONSTRAINT email_not_empty;

Validating Existing Data

When adding constraints to domains used in existing tables:

-- Add constraint without validating existing data
ALTER DOMAIN email_address 
ADD CONSTRAINT email_max_length 
CHECK (LENGTH(VALUE) <= 255)
NOT VALID;

-- Fix existing data that violates constraint
UPDATE users SET email = LEFT(email, 255) WHERE LENGTH(email) > 255;

-- Validate the constraint
ALTER DOMAIN email_address 
VALIDATE CONSTRAINT email_max_length;

Querying Domain Information

View Domain Definitions

-- List all domains
SELECT 
    n.nspname AS schema,
    t.typname AS domain_name,
    pg_catalog.format_type(t.typbasetype, t.typtypmod) AS base_type,
    pg_catalog.pg_get_constraintdef(c.oid) AS constraint_definition
FROM pg_type t
JOIN pg_namespace n ON t.typnamespace = n.oid
LEFT JOIN pg_constraint c ON c.contypid = t.oid
WHERE t.typtype = 'd'  -- 'd' = domain
ORDER BY schema, domain_name;

Find Tables Using a Domain

-- Find all columns using email_address domain
SELECT 
    n.nspname AS schema,
    c.relname AS table,
    a.attname AS column
FROM pg_attribute a
JOIN pg_class c ON a.attrelid = c.oid
JOIN pg_namespace n ON c.relnamespace = n.oid
JOIN pg_type t ON a.atttypid = t.oid
WHERE t.typname = 'email_address'
  AND a.attnum > 0
  AND NOT a.attisdropped
ORDER BY schema, table, column;

View Domain Comments

SELECT 
    typname AS domain_name,
    obj_description(oid, 'pg_type') AS description
FROM pg_type
WHERE typtype = 'd'
ORDER BY typname;

Migration Strategy

Converting Existing Columns to Domains

-- Step 1: Create the domain
CREATE DOMAIN email_address AS citext
CHECK (VALUE ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

-- Step 2: Find existing email columns
SELECT 
    table_name, 
    column_name,
    data_type
FROM information_schema.columns
WHERE column_name LIKE '%email%';

-- Step 3: Validate data before migration
SELECT COUNT(*) 
FROM users 
WHERE email !~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$';
-- If count > 0, clean data first

-- Step 4: Drop existing constraint
ALTER TABLE users DROP CONSTRAINT IF EXISTS users_email_check;

-- Step 5: Convert column type
ALTER TABLE users ALTER COLUMN email TYPE email_address;

-- Step 6: Repeat for other tables
ALTER TABLE contacts ALTER COLUMN email TYPE email_address;
ALTER TABLE subscribers ALTER COLUMN email TYPE email_address;

Handling Nullable Domains

-- Domain doesn't enforce NOT NULL
CREATE DOMAIN email_address AS TEXT
CHECK (VALUE ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

-- NOT NULL must be specified on the column
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email email_address NOT NULL,  -- NOT NULL on column, not domain
    secondary_email email_address  -- Can be NULL
);

-- Test
INSERT INTO users (id, email) VALUES (1, '[email protected]');
-- ✓ Success (secondary_email is NULL)

INSERT INTO users (id, email) VALUES (2, NULL);
-- ✗ ERROR: null value in column "email" violates not-null constraint

Common Pitfalls

Pitfall 1: Forgetting Domains Don’t Enforce NOT NULL

CREATE DOMAIN email_address AS TEXT
CHECK (VALUE ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email email_address  -- This allows NULL!
);

INSERT INTO users VALUES (1, NULL);
-- ✓ Success (but maybe not desired)

Fix:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email email_address NOT NULL  -- Explicitly add NOT NULL
);

Pitfall 2: Over-Restrictive Validation

-- ❌ TOO RESTRICTIVE: Many valid emails will fail
CREATE DOMAIN email_address AS TEXT
CHECK (VALUE ~ '^[a-z]+@[a-z]+\.[a-z]{3}$');

-- Valid emails that fail:
-- [email protected] (dots not allowed)
-- [email protected] (subdomain not allowed)
-- [email protected] (uppercase not allowed)

Fix: Use more permissive validation or citext:

CREATE DOMAIN email_address AS citext
CHECK (VALUE ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

Pitfall 3: Can’t Drop Domains in Use

CREATE DOMAIN email_address AS TEXT;

CREATE TABLE users (
    email email_address
);

-- Try to drop domain
DROP DOMAIN email_address;
-- ✗ ERROR: cannot drop type email_address because other objects depend on it

Fix: Use CASCADE (carefully!):

-- This drops the domain AND converts all columns back to base type
DROP DOMAIN email_address CASCADE;

-- Better: Migrate columns first, then drop
ALTER TABLE users ALTER COLUMN email TYPE TEXT;
DROP DOMAIN email_address;

Pitfall 4: Forgetting to Document Domains

-- ❌ NO DOCUMENTATION
CREATE DOMAIN us_phone AS TEXT
CHECK (VALUE ~ '^\d{10}$');

Fix: Always add comments:

CREATE DOMAIN us_phone AS TEXT
CHECK (VALUE ~ '^\d{10}$');

COMMENT ON DOMAIN us_phone IS 
'US phone number: exactly 10 digits, no formatting characters. 
Use normalize_us_phone() function to convert user input.
Examples: 5551234567';

When NOT to Use Domains

Case 1: Table-Specific Validation

If validation is unique to one table, use a CHECK constraint:

-- ❌ OVERKILL: Domain used in only one place
CREATE DOMAIN order_total AS NUMERIC(10, 2)
CHECK (VALUE >= 10.00);  -- Minimum order $10

CREATE TABLE orders (
    total order_total
);

Better:

CREATE TABLE orders (
    total NUMERIC(10, 2) CHECK (total >= 10.00)
);

Case 2: Complex Multi-Column Validation

Domains validate single values, not relationships:

-- ❌ CAN'T DO: Validate start_date < end_date
CREATE DOMAIN date_range AS ??? 
-- Domains don't support multi-column constraints

Better: Use CHECK constraint or tstzrange:

CREATE TABLE events (
    start_date DATE,
    end_date DATE,
    CHECK (start_date < end_date)
);

-- Or use range type (see Item 3)
CREATE TABLE events (
    event_period daterange
);

Case 3: Frequently Changing Rules

If validation rules change often, domains can be cumbersome:

-- If you're constantly altering constraints:
ALTER DOMAIN email_address DROP CONSTRAINT email_check;
ALTER DOMAIN email_address ADD CONSTRAINT email_check CHECK (...);
-- Every change affects ALL tables using this domain

Consider application-level validation if rules are unstable.

Real-World Domain Library

Here’s a starter set of commonly useful domains:

-- Enable extensions
CREATE EXTENSION IF NOT EXISTS citext;

-- Email
CREATE DOMAIN email_address AS citext
CHECK (VALUE ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

-- Phone (US)
CREATE DOMAIN us_phone AS TEXT
CHECK (VALUE ~ '^\d{10}$');

-- Postal codes
CREATE DOMAIN us_zip_code AS TEXT
CHECK (VALUE ~ '^\d{5}(-\d{4})?$');

-- URLs
CREATE DOMAIN web_url AS TEXT
CHECK (VALUE ~ '^https?://[^\s]+$' AND LENGTH(VALUE) <= 2048);

CREATE DOMAIN slug AS TEXT
CHECK (VALUE ~ '^[a-z0-9]+(-[a-z0-9]+)*$' AND LENGTH(VALUE) <= 100);

-- Money
CREATE DOMAIN money_amount AS NUMERIC(12, 2)
CHECK (VALUE >= 0);

CREATE DOMAIN percentage AS NUMERIC(5, 2)
CHECK (VALUE >= 0 AND VALUE <= 100);

-- Ratings
CREATE DOMAIN star_rating AS INTEGER
CHECK (VALUE >= 1 AND VALUE <= 5);

-- Years
CREATE DOMAIN calendar_year AS INTEGER
CHECK (VALUE >= 1900 AND VALUE <= 2100);

-- Age
CREATE DOMAIN age_years AS INTEGER
CHECK (VALUE >= 0 AND VALUE <= 150);

-- Status enums (consider ENUM type instead for fixed sets)
CREATE DOMAIN task_status AS TEXT
CHECK (VALUE IN ('pending', 'in_progress', 'completed', 'cancelled'));

-- Add comments to all
COMMENT ON DOMAIN email_address IS 'Valid email address, case-insensitive';
COMMENT ON DOMAIN us_phone IS 'US phone: 10 digits, no formatting';
-- ... etc

Summary

Key Takeaways:

  1. Use DOMAINs to define validation once, reuse everywhere
  2. Domains work in columns, arrays, functions, and composite types
  3. Add COMMENT to document domain rules and usage
  4. Domains have zero runtime overhead vs inline CHECK constraints
  5. Remember to add NOT NULL on columns (domains don’t enforce it)
  6. Use descriptive names (email_address not email)
  7. Keep validation permissive enough for real-world data
  8. Don’t use domains for table-specific or multi-column validation

The Bottom Line:

Domains are PostgreSQL’s way of creating reusable, self-documenting data types with built-in validation. They eliminate copy-paste errors, centralize validation logic, and make your schema more maintainable. Think of them as “custom primitive types” for your application’s domain model.

Further Reading

Next in the series: Item 5 – Use ENUM Types for Fixed Sets of Values

Leave a Comment