PostgreSQL Generated Columns: Automate Computed Data Without Triggers

The Problem: Manual Computed Columns Are Error-Prone

When you need derived data in PostgreSQL—like a full-text search vector, a denormalized total, or a complex metric—you often end up writing triggers or handling calculations in application code. Both approaches are brittle: triggers fire asynchronously and can fail silently, while application-level logic creates consistency gaps when multiple services write to the same table.

The Solution: GENERATED STORED Columns

PostgreSQL 12+ introduced GENERATED ALWAYS AS ... STORED columns that compute values automatically and store them physically. The key benefit: the expression is evaluated within the same transaction as the source columns, guaranteeing atomicity without triggers or application code.

Three Generation Types

• ALWAYS: Computed at storage time, but not physically stored (virtual)
• MANUAL: Computed on read, never stored
• STORED: Physically materialized and updated transactionally

Worked Example: Full-Text Search Vector

Consider a articles table where you want a tsvector column for fast full-text search:

-- Create table with generated search vector
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    body TEXT NOT NULL,
    search_vector TSVECTOR GENERATED ALWAYS AS (
        setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
        setweight(to_tsvector('english', coalesce(body, '')), 'B')
    ) STORED
);

-- Index for performance
CREATE INDEX idx_articles_search ON articles USING GIN (search_vector);

-- Test it works
INSERT INTO articles (title, body) VALUES 
    ('PostgreSQL Tips', 'Learn about generated columns and triggers');

SELECT id, title, search_vector FROM articles WHERE search_vector @@ plainto_tsquery('english', 'generated');
-- Returns the row because search_vector is automatically computed

Key Limitation: No Self-Reference

Generated columns cannot reference other generated columns. If you need multiple computed values that depend on each other, you must either: (1) compute everything in a single expression, or (2) use separate triggers. This restriction exists because PostgreSQL evaluates generated columns in dependency order, and allowing cycles would create evaluation ambiguity.

Performance Trade-Off

STORED columns have a write-time cost: every INSERT or UPDATE recalculates the expression. Complex expressions can noticeably slow writes. However, reads are faster because the computed value is pre-calculated. Use EXPLAIN ANALYZE to measure the impact:

-- Before adding generated column
EXPLAIN ANALYZE INSERT INTO articles (title, body) VALUES ('Test', 'Body');

-- After adding generated column
-- Compare execution times to assess write overhead

Practical Verification Steps

1. Check PostgreSQL version: SELECT version();; (must be 12+)
2. Create test table: Use the syntax above with a simple expression
3. Verify atomicity: Insert a row and immediately query the generated value—it should match the computed result
4. Test concurrent writes: Open two sessions and insert simultaneously to confirm the generated column stays consistent

When to Use This Approach

Generated columns shine for:

• Denormalized reporting data: Pre-compute aggregates that are expensive to calculate on read
• Full-text search vectors: Automatic tsvector maintenance without triggers
• Complex derived metrics: Hash values, JSON transformations, or formatted strings
• Indexed computed values: Create indexes directly on the generated column for performance

For cases requiring cross-column dependencies or conditional logic that's hard to express in SQL, traditional triggers may still be necessary.

Actionable Takeaway

If you're on PostgreSQL 12+ and need computed data that must stay synchronized with source columns, GENERATED ... STORED columns eliminate trigger complexity while guaranteeing consistency. Start with a simple expression, measure write performance impact, and expand to more complex use cases once you've verified the behavior in your environment.