PostgreSQL Generated Columns

Tip: Generated Column Syntax

ALTER TABLE products ADD COLUMN total_price NUMERIC
    GENERATED ALWAYS AS (quantity * unit_price) STORED;

Computed automatically on every insert/update.

Gotcha: Generated Columns Can't Be Written To

INSERT INTO products (total_price) VALUES (100); -- ERROR

You must insert the source columns, not the generated one.

Tip: Generated Columns Are Indexed

CREATE INDEX idx_products_total ON products (total_price);

Works like any regular column for indexing.

Gotcha: Only STORED is Supported

PostgreSQL only supports STORED (computed on write), not VIRTUAL (computed on read).

Tip: Use Functions in Generated Columns

GENERATED ALWAYS AS (lower(trim(name))) STORED

Any immutable function works.

Gotcha: Cannot Reference Other Tables

Generated columns can only reference columns in the same row.

Tip: EXPLAIN (ANALYZE, BUFFERS) Is Your Best Friend

For query debugging, always use EXPLAIN (ANALYZE, BUFFERS) instead of plain EXPLAIN. The BUFFERS option shows hit/miss rates for every node, revealing whether your indexes are actually in memory.

Tip: Partial Indexes Are Underutilized

CREATE INDEX ON orders (status) WHERE status = 'pending' creates a tiny index that covers only the rows your query needs. It's faster to scan and cheaper to maintain than a full-column index.

Gotcha: NULL Sorting Is Non-Obvious

By default, NULLs sort AFTER non-null values in ascending order. ORDER BY col DESC puts NULLs FIRST. Use NULLS LAST or NULLS FIRST to be explicit.

Senior Insight

Generated columns in PostgreSQL 12+ are excellent for computed data that's queried frequently. I use them for indexing JSONB field extractions, computed prices (quantity * unit_price), and formatted dates. The big advantage over application-level computation: generated columns are stored on disk and indexed naturally. The disadvantage: they add storage overhead and cannot be directly updated. For data that changes infrequently, they're a great trade-off.

Source: pganalyze Blog (https://pganalyze.com/blog), PostgreSQL Docs (https://www.postgresql.org/docs/current/), Crunchy Data Blog (https://www.crunchydata.com/blog)