# 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](https://www.postgresql.org/docs/18/ddl-generated-columns.html), > 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. ```sql 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 ); ``` ```sql 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: ```sql 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 ```sql 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. ```sql 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: ```sql 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: ```sql 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](https://www.postgresql.org/docs/18/ddl-generated-columns.html), > 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: ```sql 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](https://www.postgresql.org/docs/18/ddl-generated-columns.html), > 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](https://www.postgresql.org/docs/18/ddl-generated-columns.html), > 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](https://www.postgresql.org/docs/18/ddl-generated-columns.html), > 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: ```sql 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 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. [Get started free →](https://app.dbgorilla.com/signup) ## Related - [Which ALTER TABLE statements lock a Postgres table?](https://www.dbgorilla.com/learn/postgres/which-alter-table-statements-lock-a-postgres-table/) - [Which PostgreSQL index type should I use?](https://www.dbgorilla.com/learn/postgres/which-postgresql-index-type-should-i-use/) - [How to handle database migrations safely](https://www.dbgorilla.com/learn/postgres/how-to-handle-database-migrations-safely/)