Skip to content
Tutorials Center
My learning

Topics

Lesson 19 of 90 ยท Data Types

Generated Columns

Use PostgreSQL generated columns to keep derived values correct: STORED columns, PostgreSQL 18 virtual columns, rules, and when to prefer views or triggers.

Many tables contain a value that is calculated from other columns of the same row: a line total from quantity and price, a lower-cased email domain, a full name, a search vector. The classic solution is to store the result and keep it up to date with application code or a trigger. That works until someone updates the source column from a script and forgets the other one.

A generated column moves that responsibility into the table definition. You write the formula once, PostgreSQL computes the value, and nobody can store a value that disagrees with it. This lesson covers the two flavours (stored and, since PostgreSQL 18, virtual), the rules that come with them, and how generated columns compare with views and triggers. SQLite supports the same GENERATED ALWAYS AS syntax, so the examples run live; the PostgreSQL-specific parts are read-only blocks.

Stored generated columns

A stored generated column is computed when a row is inserted or updated, and the result is written to disk with the row:

SQL
-- PostgreSQL (read-only)
CREATE TABLE order_lines (
  id               bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  qty              integer NOT NULL,
  unit_price_cents integer NOT NULL,
  total_cents      integer GENERATED ALWAYS AS (qty * unit_price_cents) STORED
);

INSERT INTO order_lines (qty, unit_price_cents) VALUES (3, 250);
-- total_cents is 750
UPDATE order_lines SET qty = 4 WHERE id = 1;
-- total_cents is now 1000, automatically

The word STORED has been required since the feature arrived in PostgreSQL 12. Because the value is a real column on disk, you can index it, use it in a WHERE clause with full statistics, and read it without any calculation. The price is extra storage and extra work on every write.

You cannot write to a generated column. PostgreSQL refuses both INSERT and UPDATE with a non-default value:

TEXT
ERROR:  cannot insert a non-DEFAULT value into column "total_cents"
DETAIL:  Column "total_cents" is a generated column.

You can still name the column if you set it to DEFAULT, which is useful for generic code that lists every column.

SQL STORED and VIRTUAL columns in SQLite

Press Run (or Ctrl + Enter) to see the result here.

After the update, row 1 shows total_cents 1000 and the label x4 @ 2.50 without you touching either column. SQLite's VIRTUAL here is the same idea as PostgreSQL 18's virtual columns, described next.

Virtual generated columns (PostgreSQL 18)

PostgreSQL 18 added the second kind. A virtual generated column takes no storage: the expression is evaluated every time you read the column, like a tiny view built into the table.

SQL
-- PostgreSQL 18 (read-only)
CREATE TABLE people (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  first_name text NOT NULL,
  last_name  text NOT NULL,
  full_name  text GENERATED ALWAYS AS (first_name || ' ' || last_name) VIRTUAL
);

In PostgreSQL 18, if you write GENERATED ALWAYS AS (expr) with neither keyword, the column is virtual. Older versions only knew STORED, and the keyword was mandatory. Write the keyword explicitly so your DDL means the same on every version.

STORED or VIRTUAL?

STORED Computed on write, kept on disk.
VIRTUAL Computed on read, no storage. New in 18.
1 / 2

Choose by workload. If you often filter, sort or join on the value, store it: a stored column can carry an index. If the formula is cheap and the value is only displayed, virtual saves space and write time. Virtual columns in PostgreSQL 18 have restrictions that stored ones do not: you cannot index them or put them in a primary, unique or foreign key, and their expressions may only use built-in functions and types. Check the release notes for your exact version if you plan to rely on the details.

The rules for expressions

Both kinds share the same limits, and they all come from one idea: the result must be a pure function of the current row.

  • The expression may only refer to other columns of the same row that are not themselves generated. No other tables, no subqueries.
  • It must be immutable: the same inputs always give the same output. lower(email) is fine. now(), random() and anything depending on the session time zone or locale are not. That is why created_at::date on a timestamptz column is rejected, while (created_at AT TIME ZONE 'UTC')::date is accepted: the second one names its time zone.
  • A generated column cannot have a DEFAULT and cannot be part of a partition key.
  • You cannot set it in INSERT or UPDATE; it is always recomputed.

Changing a generated column later has rules of its own. Adding a stored column to a big table rewrites the table, because every existing row needs a value. ALTER TABLE t ALTER COLUMN c DROP EXPRESSION (PostgreSQL 13 and later) turns a stored column into an ordinary column that keeps its current values, and ALTER COLUMN c SET EXPRESSION AS (new_formula) (PostgreSQL 17 and later) changes the formula, rewriting stored data. See Schema Changes and Migrations for how rewrites affect locking.

Typical uses

An indexed derived key. Index what you search by. Here the domain part of an email is stored, indexed and aggregated:

SQL A generated column you can index

Press Run (or Ctrl + Enter) to see the result here.

The two example.com addresses are grouped despite the different capitalization, because the formula lower-cases the domain, and the plan line SEARCH users USING INDEX users_domain_idx (email_domain=?) shows the lookup using the index. PostgreSQL's equivalent for a case-insensitive lookup is an expression index; the generated column additionally makes the value visible in SELECT *.

Pulling hot keys out of JSON. A document column is flexible, a generated column makes one key a first-class value. This is the pattern from JSONB in Practice:

SQL
-- PostgreSQL (read-only)
ALTER TABLE products
  ADD COLUMN color text GENERATED ALWAYS AS (attrs ->> 'color') STORED;
CREATE INDEX ON products (color);

A search vector. The most famous use is a stored tsvector that is rebuilt whenever the text changes, with a GIN index on top. It is explained in Full-Text Search:

SQL
-- PostgreSQL (read-only)
ALTER TABLE articles ADD COLUMN search tsvector
  GENERATED ALWAYS AS (to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''))) STORED;
CREATE INDEX articles_search_idx ON articles USING gin (search);

Note the explicit 'english' configuration. The one-argument to_tsvector(text) depends on a setting and is not immutable, so it would be rejected here.

Here is the same idea on JSON data in SQLite. These virtual columns pull a title and a tag count out of a document, and SQLite computes them whenever you read them:

SQL Virtual columns that read from a JSON document

Press Run (or Ctrl + Enter) to see the result here.

Generated columns, views or triggers?

All three can derive a value. They differ in where the logic lives:

Generated column View Trigger
Uses other tables No Yes Yes
Can be indexed Stored: yes No (not directly) The target column can
Always consistent Yes Yes Only if the trigger is correct
Complexity One line A separate object A function plus a trigger
Writable No Sometimes n/a

Choose a generated column when the value depends only on the same row. Choose a view when you need joins, aggregates or a different shape of the data. Choose a trigger only for logic the other two cannot express, such as copying a value from another table at write time. A trigger is code you must test, and a bug leaves the data quietly wrong. The next live example shows the view alternative next to a generated column:

SQL Generated column versus view

Press Run (or Ctrl + Enter) to see the result here.

The last query shows Ada King: the generated column followed the update without any extra code. Both the column and the view solve "derived value" problems; the view could also join other tables, the column can be indexed.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which expression is NOT allowed in a generated column?

    A generated column may only depend on other non-generated columns of the same row. A subquery reads another table, so it is rejected.
  2. 2. What is the main advantage of a STORED column over a VIRTUAL one in PostgreSQL 18?

    Stored values exist on disk, so an index can be built on them. Virtual columns are computed on read and cannot be indexed in PostgreSQL 18. Neither kind allows direct writes or non-immutable functions.
  3. 3. What does GENERATED ALWAYS AS (expr) without STORED or VIRTUAL mean in PostgreSQL 18?

    From version 18 the default is virtual. Older versions required the STORED keyword, so spelling out the keyword keeps your DDL unambiguous.
  4. 4. Why is created_at::date rejected for a timestamptz column in a generated expression, but (created_at AT TIME ZONE 'UTC')::date is fine?

    Generated expressions must be immutable. A timestamptz-to-date cast uses the session time zone, so the result can change. Naming a fixed zone removes that dependency.

Summary

  • A generated column is computed from other columns of the same row; nobody can store a value that disagrees with the formula.
  • STORED columns (PostgreSQL 12+) live on disk and can be indexed; VIRTUAL columns (PostgreSQL 18, now the default) are computed on read and take no space.
  • The expression must be immutable and may only use non-generated columns of the same row: no subqueries, no now().
  • You cannot write to a generated column; use DEFAULT if you must name it.
  • Adding a stored column rewrites the table; DROP EXPRESSION exists from PostgreSQL 13 and SET EXPRESSION from 17.
  • Use a generated column for same-row values, a view for joins and reshaping, and a trigger only when nothing else fits.

Next: DISTINCT ON and Top-N per Group, the first lesson on querying with PostgreSQL's own features.