Generated Columns

What are Generated Columns?

A generated column is a column whose value is automatically computed from an expression involving other columns in the same row. PostgreSQL supports stored generated columns, which are computed on write and stored on disk.

Syntax

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  price NUMERIC(10,2),
  tax_rate NUMERIC(4,2) DEFAULT 0.10,
  total_price NUMERIC(10,2) GENERATED ALWAYS AS (price * (1 + tax_rate)) STORED
);

When to Use

• Automatically computing derived values (total = price * quantity)
• Full names from first + last name
• Ensuring derived columns are always consistent with source columns

Key Points

1. STORED — PostgreSQL only supports STORED generated columns (computed on write, saved to disk).
2. Cannot Insert/Update — You cannot set a generated column directly. It is always computed.
3. Immutable Functions — The generation expression must use immutable functions only.
4. No Subqueries — The expression cannot reference other tables or use subqueries.
5. Indexable — You can create indexes on generated columns.