In PostgreSQL 18, why does CREATE INDEX fail on full_name GENERATED ALWAYS AS (first_name || ' ' || last_name), and what fixes it?
answer
- the default kind changed in 18
- computed on read, never stored
- no index, key or statistics
- built-in, immutable expressions only
- STORED or an expression index
basics
~10 sIn PostgreSQL 18 a generated column without STORED is VIRTUAL: computed on read and never stored, so it cannot be indexed. Declare it STORED, or keep it virtual and index the same expression directly.
solid answer
~50 sSince PostgreSQL 18, `GENERATED ALWAYS AS (...)` creates a **virtual** generated column unless `STORED` is written. A virtual column occupies no storage and is computed whenever it is read, so `CREATE INDEX ... (full_name)` fails with `indexes on virtual generated columns are not supported`; the same rule blocks a primary key, `UNIQUE`, a foreign key and extended statistics on it. Two fixes: declare the column `STORED`, which computes the value on every write and keeps it on disk (adding such a column rewrites the table), or keep it virtual and build an expression index on the identical expression, which the planner can match because it expands the column into its expression. Virtual columns also accept only built-in functions and types; both kinds need immutable expressions, so `concat_ws()`, which is only stable, is rejected either way.
code
sql · 15 lines-- fails on 18: the column is virtual
ALTER TABLE contacts
ADD COLUMN full_name text
GENERATED ALWAYS AS (first_name || coalesce(' ' || last_name, ''));
CREATE INDEX ON contacts (full_name);
-- ERROR: indexes on virtual generated columns are not supported
-- fix 1: a stored column (rewrites the table)
ALTER TABLE contacts
ADD COLUMN full_name_s text
GENERATED ALWAYS AS (first_name || coalesce(' ' || last_name, '')) STORED;
CREATE INDEX ON contacts (full_name_s);
-- fix 2: keep it virtual, index the expression
CREATE INDEX ON contacts ((first_name || coalesce(' ' || last_name, '')));go deeper
Recall that PostgreSQL 18 makes generated columns virtual unless STORED is written, and that virtual columns are computed when read.
Explain what a virtual column cannot do, namely indexes, keys, statistics and user-defined functions, and the two fixes: STORED or an expression index.
Choose the kind per column from read and write patterns, plan the table rewrite a stored column costs, and write the keyword explicitly in every migration.
Set a schema convention for derived values across services, stored versus virtual versus expression indexes, and a review habit that catches releases changing a default.
## What changed in PostgreSQL 18 A **generated column** is a column whose value is always computed from other columns of the same row; you cannot write it, and only `DEFAULT` is accepted in its place. PostgreSQL 12 to 17 offered one kind, **stored**, and the `STORED` keyword was mandatory. PostgreSQL 18 adds **virtual** generated columns and makes them the **default**: ```sql -- PostgreSQL 18: VIRTUAL, because STORED is not written ALTER TABLE contacts ADD COLUMN full_name text GENERATED ALWAYS AS (first_name || ' ' || last_name); ``` A migration written from memory of the old rule now creates a different kind of column than its author expected. ## Stored versus virtual | Property | `STORED` | `VIRTUAL` (18 default) | |---|---|---| | When computed | on `INSERT` and `UPDATE` | every time the column is read | | Disk space | like a normal column | none; the catalog keeps the definition | | `ADD COLUMN` | rewrites the table | never rewrites | | Index, `PRIMARY KEY`, `UNIQUE`, foreign key | allowed | rejected | | Statistics | ordinary per-column and extended statistics | none; extended statistics are rejected | | User-defined functions or types in the expression | allowed | built-in only | | Logical replication | publishable when the publication asks for stored columns | not publishable | | `ALTER COLUMN ... DROP EXPRESSION` | turns it into a plain column | not supported | `ALTER COLUMN ... SET EXPRESSION AS (...)`, available since 17, rewrites the stored data of a stored column; a virtual column has no stored data to rewrite. ## Why the index fails, and the fixes An index stores values, and a virtual column has none, so PostgreSQL refuses with `indexes on virtual generated columns are not supported`, SQLSTATE `0A000` (`feature_not_supported`); the source comment points to expression indexes instead. Your options: 1. **Make it stored.** Write `GENERATED ALWAYS AS (...) STORED`. Every write pays for the computation and the table grows, but the column is indexable, can back a unique key and has statistics. There is no in-place switch between kinds: add a stored column, move readers to it, drop the virtual one. 2. **Keep it virtual and index the expression.** `CREATE INDEX ON contacts ((first_name || ' ' || last_name));` The planner expands a virtual column into its expression early in planning, so a filter on `full_name` can match that index when the expressions are identical; confirm with `EXPLAIN`. The expression index also gives the planner statistics for the expression, which the virtual column never has. 3. **Drop the column** if it existed only to serve the lookup; the expression index alone does that job. ## Rules both kinds share - The expression may use only **immutable** functions and operators, no subqueries, and nothing beyond the current row. `concat()` and `concat_ws()` are marked **stable**, so `GENERATED ALWAYS AS (concat_ws(' ', first_name, last_name))` fails with `generation expression is not immutable`; the `||` operator on two text values is immutable. - It may not reference another generated column, or a system column other than `tableoid`. - A generated column cannot have a default or an identity definition, and cannot be part of a partition key. - An `INSERT` or `UPDATE` that gives it a value other than `DEFAULT` fails with SQLSTATE `428C9`. - `BEFORE` triggers cannot read generated columns; the value is computed after they run, so changes they make to base columns are reflected. ## A CRM detail that bites either way `first_name || ' ' || last_name` returns **null** when either part is null, because `||` on text propagates null. Write `first_name || coalesce(' ' || last_name, '')`, still immutable, when contacts may lack a surname. ## Checking which kind a column is The catalog column `pg_attribute.attgenerated` holds `s` for stored, `v` for virtual and an empty value for an ordinary column; a virtual column is physically kept as a null and computed at run time. In psql, `\d contacts` prints `generated always as (...) stored` for a stored column and `generated always as (...)` with no keyword for a virtual one, so a missing `stored` in that output is the quickest sign of the 18 default at work. ## Choosing - **Stored** for values that are filtered, sorted, joined, constrained or replicated, and for expressions that call your own functions. - **Virtual** for cheap derivations that are read now and then, where avoiding the rewrite and the storage matters, such as a display name on a wide CRM table. - Write the keyword explicitly in every migration, so the intent does not depend on which release changed the default.
- What does adding a STORED versus a VIRTUAL generated column to a large PostgreSQL 18 table cost?Adding a stored generated column computes the value for every existing row, so it rewrites the table and its indexes, like adding a column with a volatile default. Adding a virtual one never rewrites: only the catalog changes, and the cost moves to every read that selects the column. On a big CRM table that decides whether the migration is a quick catalog change or a maintenance window.
- Why can a filter on a virtual generated column get a poor row estimate in PostgreSQL 18?Virtual columns never have statistics: `ANALYZE` has nothing stored to sample, and extended statistics on them are rejected. The planner then estimates the expanded expression with generic guesses. An expression index on the same expression makes `ANALYZE` collect statistics for that expression, and a stored column gets ordinary per-column statistics.
- GENERATED ALWAYS AS (...) is stored unless you write VIRTUAL
- A virtual generated column can back a UNIQUE constraint
- concat_ws() is fine in a generated column because it is built in
- Adding a virtual generated column rewrites the table like a stored one
- Virtual generated columns may call your own IMMUTABLE functions
Assumes PostgreSQL 18, where a generated column without STORED is virtual; PostgreSQL 12 to 17 had only stored generated columns and required the STORED keyword.