Postgres generated columns compute values automatically — here's when to use them

CREATE TABLE products (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT NOT NULL,
    price_cents INTEGER NOT NULL,
    tax_rate NUMERIC(4,2) NOT NULL DEFAULT 0.18,
    
    -- Generated column: computed from price_cents, always in sync
    price_display TEXT GENERATED ALWAYS AS (
        '$' || ROUND(price_cents / 100.0, 2)::TEXT
    ) STORED,
    
    -- Price with tax, rounded to nearest cent
    price_with_tax_cents INTEGER GENERATED ALWAYS AS (
        ROUND(price_cents * (1 + tax_rate))::INTEGER
    ) STORED
);

CREATE TABLE articles (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    title TEXT NOT NULL,
    body TEXT NOT NULL,
    tags TEXT[] NOT NULL DEFAULT '{}',
    
    -- Generated tsvector for full-text search
    -- Automatically updates when title/body/tags change
    search_vector tsvector GENERATED ALWAYS AS (
        setweight(to_tsvector('english', COALESCE(title, '')), 'A') ||
        setweight(to_tsvector('english', COALESCE(body, '')), 'B') ||
        setweight(to_tsvector('english', COALESCE(array_to_string(tags, ' '), '')), 'C')
    ) STORED
);

-- Index the generated column for fast search
CREATE INDEX idx_articles_search ON articles USING gin(search_vector);

-- Search query
SELECT title, ts_rank(search_vector, query) AS rank
FROM articles, plainto_tsquery('english', 'postgres performance') AS query
WHERE search_vector @@ query
ORDER BY rank DESC;

CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email TEXT NOT NULL,
    first_name TEXT,
    last_name TEXT,
    
    -- Always lowercase email for case-insensitive lookups
    email_normalized TEXT GENERATED ALWAYS AS (LOWER(email)) STORED,
    
    -- Full name for display
    full_name TEXT GENERATED ALWAYS AS (
        TRIM(COALESCE(first_name, '') || ' ' || COALESCE(last_name, ''))
    ) STORED
);

CREATE UNIQUE INDEX idx_users_email ON users (email_normalized);

-- Now email lookups are always case-insensitive without application logic
SELECT * FROM users WHERE email_normalized = LOWER($1);

CREATE TABLE subscriptions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL,
    started_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    expires_at TIMESTAMPTZ NOT NULL,
    
    -- Duration in days — useful for analytics
    duration_days INTEGER GENERATED ALWAYS AS (
        EXTRACT(EPOCH FROM (expires_at - started_at)) / 86400
    )::INTEGER STORED
);

-- Query subscriptions by duration tier
SELECT user_id, duration_days,
    CASE 
        WHEN duration_days <= 30 THEN 'monthly'
        WHEN duration_days <= 93 THEN 'quarterly'
        ELSE 'annual'
    END AS tier
FROM subscriptions
WHERE expires_at > NOW();