PostgreSQL Generated Columns

Derived data drifts. Someone adds an order_total column, the checkout code fills it, and six months later a support script updates quantity directly and nobody recomputes the total. Now the invoice and the database disagree. A generated column removes that whole class of bug by making the database compute the value from the same row every time, and refusing any attempt to write it by hand.

Interviewers bring this up in schema design questions (“where would you keep a computed total?”) and as a follow-up to indexing, since a generated column is one of two ways to index a derived value.

Declaring one and what PostgreSQL enforces

Here’s an order line whose total is derived from three other columns:

CREATE TABLE order_lines (
  id               bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  quantity         integer NOT NULL,
  unit_price_cents integer NOT NULL,
  discount_pct     integer NOT NULL DEFAULT 0,
  total_cents      integer GENERATED ALWAYS AS
                     (quantity * unit_price_cents * (100 - discount_pct) / 100) STORED
);

The same table works in SQLite (with integer primary key for the id), which is where the outputs in this article were produced, using SQLite 3.53 from Python. Insert two lines, then change a quantity:

INSERT INTO order_lines (quantity, unit_price_cents, discount_pct)
VALUES (3, 1999, 0), (10, 450, 20);

UPDATE order_lines SET quantity = 4 WHERE id = 1;

before update: (1, 3, 1999, 0, 5997), (2, 10, 450, 20, 3600)
after update:  (1, 7996), (2, 3600)

The total for line 1 went from 5997 to 7996 without anyone mentioning total_cents in the UPDATE. That’s the whole point: the support script from the opening paragraph can’t forget to recompute it.

Writing the column directly fails. SQLite says cannot INSERT into generated column "total_cents". PostgreSQL’s equivalent is cannot insert a non-DEFAULT value into column "total_cents", with a detail line explaining that it’s a generated column. The keyword DEFAULT is the only thing you may put in that position, which matters when an ORM insists on listing every column.

Note the integer arithmetic: 3 * 1999 * 90 / 100 would be 5397.3, and integer division truncates it to 5397. That’s a business rule hiding in an expression. If finance rounds half up, write round(... / 100.0)::integer and say so in the column comment. Generated columns make the rule consistent; they don’t make it right.

STORED or VIRTUAL

There are two kinds, and which ones you can use depends on your PostgreSQL version.

STORED computes the value when the row is inserted or updated and writes it to disk like any other column. Reads cost nothing extra. Writes cost the computation and the space. PostgreSQL 12 through 17 support only this kind, and the STORED keyword is required there.

VIRTUAL stores nothing and computes the value when a query reads it, much like a column in a view. PostgreSQL 18 introduced virtual generated columns and made them the default when you write GENERATED ALWAYS AS (...) with neither keyword. That default is worth knowing: a migration that omits the keyword fails on PostgreSQL 17 and creates a virtual column on 18, so scripts that must run on both should say STORED explicitly.

How to choose:

• Cheap expression, rarely filtered on, table is huge: virtual. You save storage and adding the column is a catalogue change rather than a table rewrite.
• Expression you want to index, constrain with UNIQUE, or read constantly: stored. PostgreSQL 18 doesn’t support indexes on virtual generated columns, so if the reason you’re adding the column is a fast lookup, it has to be stored.
• Expression that uses your own SQL functions or a custom type: stored, because virtual columns are limited to built-in functions and types.

Adding a stored generated column to an existing large table rewrites every row to fill it in, under a lock that blocks writes. Plan that like any other heavy migration.

The immutability rule

The expression may only use immutable functions and only refer to columns of the current row. No subqueries, no other tables, no other generated columns, and nothing whose result can change over time. This definition fails:

-- ERROR:  generation expression is not immutable
age_years integer GENERATED ALWAYS AS
  (extract(year FROM age(birth_date))) STORED

age(birth_date) compares against today’s date, so the “same” row would deserve a different value tomorrow. A stored column would be frozen at insert time and quietly wrong a year later; PostgreSQL refuses rather than let that happen. Compute age at query time, or in a view. Functions that depend on session settings, such as formatting a timestamptz with to_char, fail the same check because the session’s time zone changes the answer.

The same rule explains why a generated column can’t hold “the customer’s current tier” from another table. If the value depends on anything outside the row, it belongs in a view, a join, or a trigger with its own maintenance story.

Indexing a normalised lookup key

The most practical use is a column whose only job is to be searched. Emails are the classic case: Priya@Example.com and priya@example.COM should be the same account. Store what the user typed, and generate the comparison key:

CREATE TABLE users (
  id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email     text NOT NULL,
  email_key text GENERATED ALWAYS AS (lower(trim(email))) STORED
);
CREATE UNIQUE INDEX users_email_key ON users (email_key);

Inserting the second spelling after the first was rejected in the SQLite run with UNIQUE constraint failed: users.email_key, and in PostgreSQL it raises duplicate key value violates unique constraint "users_email_key". Lookups go through the index as long as you query the generated column:

lookup on email_key            -> SEARCH users USING INDEX users_email_key (email_key=?)
lookup on lower(email) instead -> SCAN users

That second line (SQLite’s query plan output) is the trap. The index is on email_key, so a query written as WHERE lower(email) = ... doesn’t match it and scans the table, even though it means the same thing. PostgreSQL behaves the same way: the planner matches an index to the expression you actually wrote.

The alternative is an expression index with no extra column: CREATE UNIQUE INDEX ON users (lower(trim(email))). It saves the column’s storage, but every query has to repeat the exact expression to use it. The generated column gives the expression a name, so application code says WHERE email_key = $1 and can’t get it subtly wrong. Teams that value that clarity pick the column; teams tight on storage pick the expression index. Either is a defensible answer if you can explain the trade.

Practice question: the discount column

A product team wants final_price_cents on products, computed from price_cents and the active discount in a promotions table (which has starts_at and ends_at). A developer proposes a stored generated column. What do you tell them?

Answer. It can’t be a generated column, for two independent reasons. The expression would read another table, and generation expressions may only see the current row. And whether a promotion is active depends on the current time, which isn’t immutable, so even with the promotion copied into the row, a stored value would go stale the moment the promotion ended. The right tool is a view or a query that joins products to promotions with now() between starts_at and ends_at. If that query is too slow, materialise it deliberately (a materialized view refreshed on a schedule, or a table maintained by the job that activates promotions) and accept the documented staleness. A good answer then points out where a generated column does fit nearby: something like price_with_vat_cents computed from price_cents and a vat_rate stored on the same row.

Where to next

• SQL interview questions and SQL MCQs
• Constraints as invariants, the wider idea of letting the schema enforce rules
• Partial indexes, another way to index exactly the rows and values you query
• PostgreSQL docs: generated columns