What are generated columns?

Question

IT Interview Practice · Accepted Answer

A **generated column** is a column whose value is **computed automatically** from other columns, rather than being inserted directly. PostgreSQL supports **stored** generated columns (Postgres 12+) — the computed value is calculated on write and stored. They keep derived data consistent automatically.

## Defining a generated column

```sql
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  price NUMERIC,
  quantity INT,
  -- a STORED generated column — computed from price and quantity
  total NUMERIC GENERATED ALWAYS AS (price * quantity) STORED
);

INSERT INTO products (price, quantity) VALUES (10, 3);
SELECT total FROM products;          -- 30 — computed automatically (you didn't insert it)
```

The `GENERATED ALWAYS AS (expression) STORED` clause makes `total` always equal `price * quantity` — computed on insert/update and stored. You can't (and don't) insert into it directly; it's always derived.

## Common uses

```sql
-- a full name from parts
full_name TEXT GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED

-- a normalized/lowercased value for searching
email_lower TEXT GENERATED ALWAYS AS (lower(email)) STORED

-- area from dimensions
area NUMERIC GENERATED ALWAYS AS (width * height) STORED

-- extract a value from JSONB into a real column (then index it)
user_id INT GENERATED ALWAYS AS ((data->>'user_id')::int) STORED
```

Generated columns are great for derived values you query often — they stay consistent automatically and can be **indexed** (since stored).

## Generated columns vs computing in queries vs triggers

```text
Generated column → derived value computed automatically, stored, indexable, consistent
Computing in a query (price*qty in SELECT) → no storage, but recomputed every query,
                                              can't be indexed directly
Trigger → more flexible but more complex; generated columns are simpler for pure derivations
→ Use generated columns for simple, deterministic derived values you query/index often.
```

## Why it matters

Generated columns are a useful PostgreSQL feature for **automatically maintaining derived data**, and understanding them is valuable for keeping computed values consistent and queryable.

The core benefit is that a generated column's value is always automatically computed from other columns and kept in sync — so derived data (totals, full names, normalized values, extracted JSONB fields) stays **consistent by construction** without application code or manual updates having to remember to recompute it (eliminating a class of bugs where derived data drifts out of sync).

Because stored generated columns physically store the value, they can also be **indexed** — which is particularly valuable for things like a lowercased email for case-insensitive search, or extracting a JSONB field into a real, indexable column (a powerful pattern combining JSONB flexibility with relational indexing).

Understanding when to use generated columns versus alternatives — generated columns for simple, deterministic derived values you query or index frequently (consistent, indexable, simple), versus computing in queries (no storage but recomputed each time and not directly indexable) or triggers (more flexible but more complex) — reflects sound design judgment.

Since maintaining derived/computed data consistently is a common need, and since generated columns provide an automatic, consistent, indexable way to do it (cleaner than manual maintenance or triggers for simple derivations), understanding generated columns is valuable, practically-relevant PostgreSQL knowledge for designing schemas where computed values are kept correct automatically and made efficiently queryable — a useful modern Postgres feature for clean, consistent data modeling.

Defining a generated column

Common uses

Generated columns vs computing in queries vs triggers

Why it matters

Related questions