Why can't I index a virtual generated column in Postgres 18?

Quick answer: PostgreSQL 18 does not permit an index whose key is a virtual generated column. An expression index over the same expression stores its own keys and works. The first trap is that VIRTUAL is now the default where omitting STORED used to be a syntax error. The second is that the expression index is not free: measured on 2026-09-20 on PostgreSQL 18.6 in a postgres:18 container, it cost the same write time as a stored column.

What is the problem?

A generated column gets its value from an expression over other columns in the same row. PostgreSQL has had them since 12, in one form: STORED, which computes the value on write and keeps it on disk like any other column.

PostgreSQL 18 added VIRTUAL, which computes the value on read and stores nothing in the heap, and made it the default.

“A generated column is by default of the virtual kind.” (PostgreSQL 18 documentation, Generated Columns, read on 2026-09-20)

Everything below was measured on 2026-09-20 on PostgreSQL 18.6, image postgres:18, in container dbg-pg18-verify, on a host with 8 vCPUs and 62 GB of RAM running Linux 6.17.0-1022-azure under Docker 29.1.3.

CREATE TABLE gc (
  id          int PRIMARY KEY,
  first_name  text,
  last_name   text,
  full_name   text GENERATED ALWAYS AS (first_name || ' ' || last_name) VIRTUAL,
  full_stored text GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED
);

INSERT INTO gc (id, first_name, last_name) VALUES (1, 'ada', 'lovelace');
SELECT * FROM gc;

Measured on 2026-09-20 on that PostgreSQL 18.6 postgres:18 container, both columns hold the same value:

 id | first_name | last_name |  full_name   | full_stored
----+------------+-----------+--------------+--------------
  1 | ada        | lovelace  | ada lovelace | ada lovelace

The difference appears when you try to index one:

CREATE INDEX idx_gc_full ON gc (full_name);
ERROR:  indexes on virtual generated columns are not supported

ALTER TABLE gc ADD CONSTRAINT u UNIQUE (full_name);
ERROR:  unique constraints on virtual generated columns are not supported

CREATE INDEX idx_gc_stored ON gc (full_stored); on the same table succeeded.

Nothing being stored does not mean there is nothing to index: an expression index stores its own computed keys and never reads a heap value. PostgreSQL does not support an index whose key is a reference to a virtual generated column. Treat it as a restriction with a workaround.

The part that will actually catch you

Omit the keyword and PostgreSQL 18 gives you a virtual column:

CREATE TABLE gd (a int, b int GENERATED ALWAYS AS (a * 2));
SELECT attname, attgenerated
FROM pg_attribute
WHERE attrelid = 'gd'::regclass AND attgenerated <> '';

That returns b | v. The v is virtual. The same statement on PostgreSQL 16.15 and on 17.11, both measured on 2026-09-20 in postgres:16 and postgres:17 containers:

ERROR:  syntax error at or near ")"
LINE 1: CREATE TABLE gd (a int, b int GENERATED ALWAYS AS (a*2));

The default changed from “this is not valid SQL” to “this is a virtual column”. A migration written against 17 that forgot STORED failed at the first attempt. On 18 the same migration succeeds.

Where the failure arrives depends on the migration. If the same deployment also creates the index or the unique constraint, it fails immediately. If the index is added later or by a different team, the failure arrives then.

Auditing what you have

SELECT c.relname AS table_name,
       a.attname AS column_name,
       CASE a.attgenerated WHEN 's' THEN 'stored' WHEN 'v' THEN 'virtual' END AS kind
FROM pg_attribute a
JOIN pg_class c ON c.oid = a.attrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE a.attgenerated <> ''
  AND NOT a.attisdropped
  AND n.nspname = 'public'
ORDER BY c.relname, a.attnum;

Be precise about the catalog value. attgenerated is the "char" type, and for an ordinary column it holds the NUL character, not an SQL empty string. Measured on 2026-09-20 on that PostgreSQL 18.6 postgres:18 container, ascii(attgenerated) is 0 for an ordinary column, 118 for v and 115 for s, and attgenerated = '' is true for the ordinary column. So the <> '' predicate works because NUL compares equal to the empty string here, not because the column is blank.

The NOT a.attisdropped clause is good practice. It changed nothing on this server: a dropped column’s entry had attgenerated = '' already, so the first predicate excluded it. Keep it anyway.

The workaround, and what it actually costs

You cannot index the column. You can index the expression, and the planner connects the two.

CREATE INDEX idx_gc_expr ON gc ((first_name || ' ' || last_name));

Measured on 2026-09-20 on that PostgreSQL 18.6 postgres:18 container, with 49,999 more rows loaded and the table analysed, a query written against the virtual column used it:

EXPLAIN (ANALYZE, COSTS OFF, BUFFERS) SELECT id FROM gc WHERE full_name = 'f99 l99';

 Index Scan using idx_gc_expr on gc (actual time=0.021..0.022 rows=1.00 loops=1)
   Index Cond: (((first_name || ' '::text) || last_name) = 'f99 l99'::text)
   Index Searches: 1
   Buffers: shared hit=4
 Execution Time: 0.050 ms

Read the Index Cond, not the time. The planner expanded the virtual column into its definition and matched that against the expression index.

The expression index moves materialisation from the heap into the index, so the write cost comes with it. Measured on 2026-09-20 on that PostgreSQL 18.6 postgres:18 container, inserting 500,000 rows into three designs of the same column:

design	insert of 500,000 rows	total relation size
virtual column, no index	625.621 ms	17 MB
virtual column plus an expression index	2935.232 ms	36 MB
stored column plus a plain index	2919.015 ms	40 MB

Measured on 2026-09-20 on that PostgreSQL 18.6 postgres:18 container, the expression index cost 4.7 times the write time of the bare virtual column. That was the same write time as the stored column, while saving 4 MB of 40. If you need the search, storage is no longer an argument for VIRTUAL.

Two conditions on the index being used

The expressions have to match. Use the same expression text and validate with EXPLAIN. PostgreSQL does not prove arbitrary semantic equivalence, so do not assume two expressions that give the same answer are interchangeable.

concat_ws(' ', first_name, last_name) is the usual example of an expression that will not match ||. The problem arrives earlier than that: concat_ws and concat both have provolatile of s, so the index cannot be created at all. Measured on 2026-09-20 on that PostgreSQL 18.6 postgres:18 container:

CREATE INDEX gc_ws ON gc ((concat_ws(' ', first_name, last_name)));
ERROR:  functions in index expression must be marked IMMUTABLE

The expression has to be immutable. That means checking the volatility of every function and operator in it. “No timezone conversions” is wrong. Measured on 2026-09-20 on that PostgreSQL 18.6 postgres:18 container:

generated column expression	result
t::date where t is timestamptz	ERROR: generation expression is not immutable
t AT TIME ZONE 'UTC' where t is timestamptz	accepted
t::date where t is timestamp	accepted

The difference is which overload you reach. pg_proc on that server lists both immutable and stable variants of timezone and of date, so the answer depends on argument types rather than on the word “timezone”. Check the one you are about to use:

SELECT oid::regprocedure, provolatile FROM pg_proc WHERE proname = 'date';

Uniqueness: an expression index is not a constraint

A unique expression index enforces uniqueness. Measured on 2026-09-20 on that PostgreSQL 18.6 postgres:18 container, the second insert was rejected:

CREATE UNIQUE INDEX gc_full_uq ON gc ((first_name || ' ' || last_name));

INSERT INTO gc (id, first_name, last_name) VALUES (50001,'ada','lovelace');
ERROR:  duplicate key value violates unique constraint "gc_full_uq"
DETAIL:  Key (((first_name || ' '::text) || last_name))=(ada lovelace) already exists.

Row 1 is ada lovelace, inserted at the top of this page. The new row carries a primary key that nothing is using, so it collides only with the expression.

It is not a named UNIQUE or PRIMARY KEY constraint, which matters because a foreign key cannot point at it. Measured on 2026-09-20 on that PostgreSQL 18.6 postgres:18 container, both attempts failed:

CREATE TABLE child (k text, CONSTRAINT fk FOREIGN KEY (k) REFERENCES gc (full_name));
ERROR:  there is no unique constraint matching given keys for referenced table "gc"

CREATE TABLE child (k text REFERENCES gc ((first_name || ' ' || last_name)));
ERROR:  syntax error at or near "("

The second is not expressible. If the derived value has to be a foreign key target, it has to be a real column with a real unique constraint, which means STORED.

Partition keys: STORED does not help

The common advice is to use a stored column if you want a partition key on the value. That is wrong, and the documentation says so.

“A generated column cannot be part of a partition key.” (PostgreSQL 18 documentation, Generated Columns, read on 2026-09-20)

Measured on 2026-09-20 on that PostgreSQL 18.6 postgres:18 container, both kinds are refused with the same message:

CREATE TABLE p1 (a int, k int GENERATED ALWAYS AS (a*2) STORED) PARTITION BY RANGE (k);
ERROR:  cannot use generated column in partition key
DETAIL:  Column "k" is a generated column.

Partition by the expression instead, which the same server accepted:

CREATE TABLE p3 (a int) PARTITION BY RANGE ((a * 2));

Whether an expression is usable depends on its immutability, so a production schema commonly partitions on a base column or on a deliberately maintained ordinary column rather than on a derived one.

What else a virtual column cannot do

These restrictions make STORED to VIRTUAL a schema-validity change rather than a storage choice. All measured on 2026-09-20 on that PostgreSQL 18.6 postgres:18 container.

attempt on a virtual generated column	result	the same as STORED
a user-defined function in the expression	ERROR: generation expression uses user-defined function	accepted
a user-defined type as the column type	ERROR: virtual generated column "b" cannot have a user-defined type	accepted
referencing another generated column	ERROR: cannot use generated column "b" in column generation expression	also refused
a subquery in the expression	ERROR: cannot use subquery in column generation expression	also refused
CREATE INDEX	indexes on virtual generated columns are not supported	accepted
UNIQUE constraint	unique constraints on virtual generated columns are not supported	accepted
PRIMARY KEY	primary keys on virtual generated columns are not supported	accepted
partition key	cannot use generated column in partition key	also refused

Both user-defined errors carry the same detail line, not yet supported, so these may change in a later release. They are the current behaviour of 18.6.

No statistics

Measured on 2026-09-20 on that PostgreSQL 18.6 postgres:18 container, after ANALYZE on a table with one ordinary, one virtual and one stored column, pg_stats held rows for the ordinary and the stored column and none for the virtual one. For a skewed derived value, that is an estimation problem. Validate plans on representative data rather than on a point lookup.

No logical replication

“Generated columns are allowed to be replicated during logical replication according to the CREATE PUBLICATION parameter publish_generated_columns or by including them in the column list of the CREATE PUBLICATION command. This is currently only supported for stored generated columns.” (PostgreSQL 18 documentation, Generated Columns, read on 2026-09-20)

The server reports this directly. Measured on 2026-09-20 on that PostgreSQL 18.6 postgres:18 container, the option refuses the word:

CREATE PUBLICATION pub_x FOR TABLE gc WITH (publish_generated_columns = virtual);
ERROR:  invalid value for publication parameter "publish_generated_columns": "virtual"
DETAIL:  Valid values are "none" and "stored".

If the schema is replicated, a virtual column is a value the subscriber will never see.

Privileges work, with a caveat worth reading

“Generated columns maintain access privileges separately from their underlying base columns.” (PostgreSQL 18 documentation, Generated Columns, read on 2026-09-20)

Measured on 2026-09-20 on that PostgreSQL 18.6 postgres:18 container, a role granted SELECT (full_name) read the virtual column and was refused first_name, so this can expose a derived or redacted value.

“For virtual generated columns, this is only fully secure if the generation expression uses only leakproof functions (see CREATE FUNCTION), but this is not enforced by the system.” (PostgreSQL 18 documentation, Generated Columns, read on 2026-09-20)

Nothing here tests that leak. It comes from the documentation, not a measurement, and it is a reason not to treat a virtual column as a security boundary without checking the volatility and leakproofness of its expression.

Converting a virtual column to stored

There is no ALTER that changes the kind in place, so conversion requires a replacement. Dropping the column also drops its dependents.

Measured on 2026-09-20 on that PostgreSQL 18.6 postgres:18 container, on a table with a view over the virtual column, the plain drop was refused and the CASCADE took the view:

ALTER TABLE dep DROP COLUMN full_name CASCADE;
NOTICE:  drop cascades to view dep_v

An expression index over the same expression survived because it depends on the base columns rather than on the generated one.

Adding the stored column rewrites the table. Measured on 2026-09-20 on that PostgreSQL 18.6 postgres:18 container by comparing relfilenode before and after:

statement	relfilenode changed	means
ADD COLUMN ... VIRTUAL	no	no rewrite
ADD COLUMN ... STORED	yes	full rewrite under ACCESS EXCLUSIVE
ALTER COLUMN ... SET EXPRESSION on a virtual column	no	no rewrite
ALTER COLUMN ... SET EXPRESSION on a stored column	yes	full rewrite

The last two rows are the one place virtual is cheaper to change: redefining a virtual column is metadata, while redefining a stored one rewrites the table.

On a table you cannot lock, do not write the drop and the add in one migration. Add a new column under its own name, build the index with CREATE INDEX CONCURRENTLY where applicable, move the application’s reads, verify, and drop the old column in a later deployment. Find the dependents first. DISTINCT matters here because pg_depend holds one row per referenced column and a view over two of them appears twice:

SELECT DISTINCT dependent.relname, dependent.relkind
FROM pg_depend d
JOIN pg_rewrite r ON r.oid = d.objid
JOIN pg_class dependent ON dependent.oid = r.ev_class
WHERE d.refobjid = 'gc'::regclass AND d.refobjsubid <> 0;

When to use each

Use STORED when the value is read more than it is written, when you need to index it, constrain it, reference it with a foreign key, replicate it, or have statistics on it, and whenever the expression uses your own functions or types. That covers most cases.

Use VIRTUAL when the expression is cheap, the column is written far more often than it is read, nothing searches on it, and none of the restrictions above apply. The gain is narrow: no heap storage, no write cost, and no table rewrite when you add it or redefine it.

Ask whether you will put this column in a WHERE clause. If so, decide whether you will pay the write cost through an index instead of through the heap.

Why does AI-generated code cause this?

The version gap causes most of it.

A model calibrated on PostgreSQL 12 through 17 writes GENERATED ALWAYS AS (...) STORED, because that was the only valid form, and that code is correct on 18 as well.

A model that knows 18 added virtual columns may write the shorter form without the keyword because it now parses. On 18 that gives you a virtual column. The difference surfaces when somebody adds an index.

The second pattern is the confident fix. Given the index error, the natural suggestion is an ALTER that adds STORED to the definition, which PostgreSQL does not support. Another suggestion is that a stored column will let you partition on it. Measured on 2026-09-20 on that PostgreSQL 18.6 postgres:18 container, PostgreSQL refused a stored generated column as a partition key with the same message it gives for a virtual one.

How do I stop it coming back?

Always write the keyword. STORED or VIRTUAL, explicitly, in every generated column definition. This removes a version-dependent default from your schema.

Audit attgenerated after upgrading to 18, and again after any migration that adds generated columns.

Create the index in the same migration as the column, if you are keeping it virtual and might search it. Then a failed deployment exposes the problem immediately.

Check the volatility of what you put in the expression, with pg_proc.provolatile, rather than reasoning about categories of function.

Decide on the read path, not the write path, and price the expression index honestly. On the numbers above it is not a cheaper STORED.

How DBGorilla helps

DBGorilla can help

The decision on this page is about the read path, and the read path is not in the schema file. DBGorilla connects read-only and gives your AI coding agent the schema and its history over time, index usage and scan counts, table and index sizes, and the workload by query fingerprint, so which columns are actually filtered on is an observation rather than a guess. All of that was verified in a live session.

Start free

FAQ

• Why can't I index a virtual generated column?

  Because PostgreSQL 18 does not support it, which is a product restriction rather than a physical impossibility. Measured on 2026-09-20 on PostgreSQL 18.6 in a postgres:18 container, CREATE INDEX on one returns 'ERROR: indexes on virtual generated columns are not supported' and a unique constraint returns the equivalent message. An expression index over the same expression does store its own keys, and it works.

• Is VIRTUAL really the default in Postgres 18?

  Yes. Measured on 2026-09-20 on PostgreSQL 18.6 in a postgres:18 container, GENERATED ALWAYS AS (expr) with no keyword after it creates a virtual column. The same statement is a syntax error on 16.15 and 17.11, because STORED was mandatory. The error does not have to reach production: a migration that creates the index in the same deployment fails right there.

• Is an expression index a free way to index a virtual column?

  No, and this is the number that should decide the design. Measured on 2026-09-20 on PostgreSQL 18.6 in a postgres:18 container, inserting 500,000 rows took 625.621 ms into a virtual column with no index and 17 MB, 2935.232 ms with an expression index and 36 MB, and 2919.015 ms into a stored column with a plain index and 40 MB. The expression index costs the same write time as STORED and saves 4 MB.

• Can I make a generated column a partition key if I make it STORED?

  No. Measured on 2026-09-20 on PostgreSQL 18.6 in a postgres:18 container, both a STORED and a VIRTUAL generated column are refused with 'ERROR: cannot use generated column in partition key'. Partition by the expression itself instead, which the same server accepted as PARTITION BY RANGE ((a*2)).

• Can a virtual column do everything a stored one can, apart from indexing?

  No. Measured on 2026-09-20 on PostgreSQL 18.6 in a postgres:18 container, a virtual generated column refused a user-defined function and a user-defined type, both of which the same expression accepted as STORED. It also has no column statistics after ANALYZE, and it cannot be published by logical replication at all.

• How do I convert a virtual column to stored?

  Replace it rather than altering it, and plan the dependencies. Measured on 2026-09-20 on PostgreSQL 18.6 in a postgres:18 container, DROP COLUMN on a virtual column with a dependent view was refused until CASCADE was added, and then it dropped the view. Adding the stored column changed the table's relfilenode, which is a full rewrite under an ACCESS EXCLUSIVE lock.

Related

• Which ALTER TABLE statements lock a Postgres table?
• Which PostgreSQL index type should I use?
• How to handle database migrations safely