PostgreSQL 18 Virtual Generated Columns Guide

September 28, 2026 by Chat2DB

PostgreSQL 12 introduced generated columns, but only one kind: STORED. The value was computed on INSERT and UPDATE and written to disk like any other column. PostgreSQL 18 adds the second kind, VIRTUAL, where the value is computed when the row is read and nothing is written to the table. It also makes VIRTUAL the default when you omit the keyword.

That default change matters. A CREATE TABLE script that relied on the old requirement to write STORED still works, but new code that leaves the keyword out gets different storage, different indexing rules, and different ALTER TABLE behavior than you might expect.

This guide walks through how virtual generated columns behave in practice. Every statement, error message, and plan below was run on a PostgreSQL 18.6 server, and the output is copied as printed. If you want the broader background on generated columns (JSONB extraction, tsvector search columns, migrations), read the companion article PostgreSQL Generated Columns: STORED and VIRTUAL Explained first.

What a Virtual Generated Column Is

A generated column is a column whose value is always derived from other columns in the same row. You never write to it directly. The two kinds differ only in when the expression is evaluated:

Kind	Evaluated	Uses disk space	Can be indexed
`STORED`	On `INSERT` / `UPDATE`	Yes	Yes
`VIRTUAL`	On read (like a view column)	No	No (index the expression instead)

You can think of a virtual column as a view column that lives inside the table definition. The planner substitutes the expression wherever the column is referenced.

Step 1: Create a Table Without Specifying the Kind

Start a psql session on a PostgreSQL 18 server and create a table that leaves out both STORED and VIRTUAL:

CREATE TABLE orders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  qty int NOT NULL,
  unit_price numeric(10,2) NOT NULL,
  total numeric GENERATED ALWAYS AS (qty * unit_price)
);

On PostgreSQL 17 and earlier this statement fails because STORED was mandatory. On PostgreSQL 18 it succeeds, and the column is virtual. You can confirm it in the catalog, where attgenerated is v for virtual and s for stored:

SELECT attname, attgenerated
FROM pg_attribute
WHERE attrelid = 'orders'::regclass AND attnum > 0;

  attname   | attgenerated
------------+--------------
 id         |
 qty        |
 unit_price |
 total      | v
(4 rows)

The standard information_schema view also shows the column and its expression, but it does not distinguish the two kinds, so use pg_attribute when you need to know which one you have:

SELECT column_name, is_generated, generation_expression
FROM information_schema.columns
WHERE table_name = 'orders'
ORDER BY ordinal_position;

In psql, \d orders prints the expression with type casts filled in:

 total      | numeric       |           |          | generated always as (qty::numeric * unit_price)

The practical advice: always write the keyword explicitly. GENERATED ALWAYS AS (...) VIRTUAL or GENERATED ALWAYS AS (...) STORED makes the intent obvious to reviewers and to anyone porting the schema to an older version.

Step 2: Insert and Update Data

Reading and writing behave the same way for both kinds. You insert the base columns, and the generated column appears in results:

INSERT INTO orders (qty, unit_price)
VALUES (3, 9.99), (10, 1.50)
RETURNING *;

 id | qty | unit_price | total
----+-----+------------+-------
  1 |   3 |       9.99 | 29.97
  2 |  10 |       1.50 | 15.00
(2 rows)

Trying to supply a value is rejected:

INSERT INTO orders (qty, unit_price, total) VALUES (1, 1, 1);

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

The keyword DEFAULT is allowed, which helps when an ORM or a bulk loader always lists every column:

INSERT INTO orders (qty, unit_price, total) VALUES (1, 1, DEFAULT) RETURNING *;

UPDATE follows the same rule:

UPDATE orders SET total = 5 WHERE id = 1;

ERROR:  column "total" can only be updated to DEFAULT
DETAIL:  Column "total" is a generated column.

Step 3: See How the Planner Expands the Column

Because the value is not stored, every query that references total gets the expression substituted in. EXPLAIN (VERBOSE) makes this visible:

CREATE TABLE big AS
SELECT g AS id, g % 100 AS qty FROM generate_series(1, 100000) g;
 
ALTER TABLE big ADD COLUMN q2 int GENERATED ALWAYS AS (qty * 2) VIRTUAL;
 
EXPLAIN (VERBOSE, COSTS OFF) SELECT q2 FROM big WHERE q2 > 100;

           QUERY PLAN
---------------------------------
 Seq Scan on public.big
   Output: (qty * 2)
   Filter: ((big.qty * 2) > 100)
(3 rows)

There is no q2 in the plan at all, only qty * 2. This has two consequences:

The cost of the expression is paid on every read, for every row the query touches. Cheap arithmetic is effectively free. An expensive expression on a hot query path is not.
Any feature that works on expressions also works for virtual columns, which is the key to indexing them (Step 5).

Step 4: Compare Storage With a STORED Column

The main benefit of VIRTUAL is that it takes no space in the heap. To see it, create two tables with one million rows each, one with a stored bigint generated column and one with a virtual one:

CREATE TABLE v1 AS SELECT g AS a FROM generate_series(1, 1000000) g;
ALTER TABLE v1 ADD COLUMN b bigint GENERATED ALWAYS AS (a::bigint * 7) VIRTUAL;
 
CREATE TABLE s1 (a int, b bigint GENERATED ALWAYS AS (a::bigint * 7) STORED);
INSERT INTO s1 (a) SELECT g FROM generate_series(1, 1000000) g;
 
SELECT pg_size_pretty(pg_relation_size('v1')) AS virtual_table,
       pg_size_pretty(pg_relation_size('s1')) AS stored_table;

 virtual_table | stored_table
---------------+--------------
 35 MB         | 42 MB

The difference is the eight bytes (plus alignment) of the stored bigint in every row. With wide expressions such as concatenated strings or extracted JSON text, the gap grows. Your exact numbers depend on row width and alignment, so run the same comparison on a sample of your own data before deciding.

Step 5: Indexing a Virtual Column

This is where most people hit the first wall. A virtual column cannot be indexed directly:

CREATE INDEX ON orders (total);

ERROR:  indexes on virtual generated columns are not supported

The same restriction applies to unique constraints, which are backed by indexes:

ALTER TABLE orders ADD CONSTRAINT total_uq UNIQUE (total);

ERROR:  unique constraints on virtual generated columns are not supported

Use an Expression Index Instead

Since the planner rewrites the column into its expression, an index on the same expression is matched automatically. Build a table with enough rows for the planner to care:

CREATE TABLE sales (
  id int PRIMARY KEY,
  qty int,
  price numeric,
  total numeric GENERATED ALWAYS AS (qty * price) VIRTUAL
);
 
INSERT INTO sales
SELECT g, g % 50, (g % 1000) / 10.0
FROM generate_series(1, 200000) g;
 
CREATE INDEX sales_total_expr ON sales ((qty * price));
ANALYZE sales;
 
EXPLAIN (COSTS OFF) SELECT id FROM sales WHERE total > 4900;

                         QUERY PLAN
------------------------------------------------------------
 Index Scan using sales_total_expr on sales
   Index Cond: (((qty)::numeric * price) > '4900'::numeric)
(2 rows)

The query filters on total, the index was built on (qty * price), and the planner connects the two. For this to work the index expression must match the generation expression after PostgreSQL normalizes it, so copy the expression exactly. If you are unsure of the precise syntax, the PostgreSQL CREATE INDEX generator (opens in a new tab) can produce the statement for you.

When You Need a Real Index on the Column

If you need a unique constraint on the derived value, need a foreign key on it, or simply want the index to be obviously tied to the column name, use STORED:

CREATE TABLE orders_s (
  qty int,
  unit_price numeric,
  total numeric GENERATED ALWAYS AS (qty * unit_price) STORED
);
CREATE INDEX ON orders_s (total);

That index is created without complaint.

Step 6: Constraints That Do and Do Not Work

Not every constraint is off the table. Here is what PostgreSQL 18.6 accepted and rejected in testing.

Allowed: NOT NULL and CHECK

ALTER TABLE orders ADD CONSTRAINT total_pos CHECK (total > 0);
ALTER TABLE orders ALTER COLUMN total SET NOT NULL;

Both succeed. The constraints are evaluated against the computed value, and the error message shows virtual in place of the value in the failing row:

CREATE TABLE t_ck (a int, b int GENERATED ALWAYS AS (a * 2) VIRTUAL CHECK (b < 100));
INSERT INTO t_ck VALUES (60);

ERROR:  new row for relation "t_ck" violates check constraint "t_ck_b_check"
DETAIL:  Failing row contains (60, virtual).

Rejected: Foreign Keys, Extended Statistics, Partition Keys

CREATE TABLE fkref (v numeric PRIMARY KEY);
CREATE TABLE fkt (
  a numeric,
  b numeric GENERATED ALWAYS AS (a) VIRTUAL REFERENCES fkref (v)
);

ERROR:  foreign key constraints on virtual generated columns are not supported

CREATE STATISTICS s1 ON total, qty FROM orders;

ERROR:  statistics creation on virtual generated columns is not supported

CREATE TABLE pt (a int, b int GENERATED ALWAYS AS (a * 2) VIRTUAL)
PARTITION BY RANGE (b);

ERROR:  cannot use generated column in partition key
DETAIL:  Column "b" is a generated column.

The partition key restriction applies to stored generated columns as well. For extended statistics, you can create statistics on the underlying expression instead, just as with indexes.

Step 7: Restrictions on the Expression

All generated columns require an immutable expression that only references the current row. PostgreSQL 18 adds a few extra rules for the virtual kind.

No User-Defined Functions

CREATE FUNCTION dbl(int) RETURNS int IMMUTABLE LANGUAGE sql AS 'SELECT $1 * 2';
 
CREATE TABLE t_fn (a int, b int GENERATED ALWAYS AS (dbl(a)) VIRTUAL);

ERROR:  generation expression uses user-defined function
DETAIL:  Virtual generated columns that make use of user-defined functions are not yet supported.

The same function works in a STORED column. If your expression depends on a custom function (a slug helper, a normalizer, a custom hash), you must use STORED in PostgreSQL 18.

No User-Defined Types or Domains

CREATE DOMAIN posnum AS numeric CHECK (VALUE > 0);
CREATE TABLE t_dom (a numeric, b posnum GENERATED ALWAYS AS (a) VIRTUAL);

ERROR:  virtual generated column "b" cannot have a domain type

CREATE TYPE mytype AS (x int, y int);
CREATE TABLE t_ty (a int, b mytype GENERATED ALWAYS AS (ROW(a, a)::mytype) VIRTUAL);

ERROR:  virtual generated column "b" cannot have a user-defined type
DETAIL:  Virtual generated columns that make use of user-defined types are not yet supported.

The wording "not yet supported" suggests these limits may be lifted in a later release, but for PostgreSQL 18 plan around them.

Rules Shared With STORED

These apply to both kinds:

CREATE TABLE t_vol (a int, b timestamptz GENERATED ALWAYS AS (now()) VIRTUAL);

ERROR:  generation expression is not immutable

CREATE TABLE t_ref (
  a int,
  b int GENERATED ALWAYS AS (a * 2) VIRTUAL,
  c int GENERATED ALWAYS AS (b + 1) VIRTUAL
);

ERROR:  cannot use generated column "b" in column generation expression
DETAIL:  A generated column cannot reference another generated column.

Write c in terms of the base columns instead: GENERATED ALWAYS AS (a * 2 + 1).

Step 8: ALTER TABLE Behavior

This is where VIRTUAL has a clear operational advantage on large tables. A useful way to see whether PostgreSQL rewrote a table is to watch relfilenode, which changes whenever the table gets a new physical file.

SELECT relfilenode FROM pg_class WHERE relname = 'big';
-- 16425
 
ALTER TABLE big ADD COLUMN q2 int GENERATED ALWAYS AS (qty * 2) VIRTUAL;
SELECT relfilenode FROM pg_class WHERE relname = 'big';
-- 16425  (no rewrite)
 
ALTER TABLE big ADD COLUMN q3 int GENERATED ALWAYS AS (qty * 3) STORED;
SELECT relfilenode FROM pg_class WHERE relname = 'big';
-- 16430  (table rewritten)

The numbers are from the test server; yours will differ, but the pattern is the same. Adding a virtual column is a catalog-only change. Adding a stored column rewrites every row, holding an ACCESS EXCLUSIVE lock for the duration.

Changing the Expression

ALTER COLUMN ... SET EXPRESSION (available since PostgreSQL 17) behaves the same way:

ALTER TABLE big ALTER COLUMN q2 SET EXPRESSION AS (qty * 4);  -- virtual: no rewrite
ALTER TABLE big ALTER COLUMN q3 SET EXPRESSION AS (qty * 5);  -- stored: rewrite

In testing, relfilenode stayed the same after the first statement and changed after the second.

There is one catch. If the table has a check constraint, changing a virtual column expression is refused, because PostgreSQL would have to re-validate the constraint against values it never stored:

ALTER TABLE t_ck ALTER COLUMN b SET EXPRESSION AS (a * 3);

ERROR:  ALTER TABLE / SET EXPRESSION is not supported for virtual generated columns in tables with check constraints
DETAIL:  Column "b" of relation "t_ck" is a virtual generated column.

Drop the check constraint, change the expression, and re-add the constraint (optionally NOT VALID followed by VALIDATE CONSTRAINT to reduce lock time).

Dropping the Expression

DROP EXPRESSION turns a stored generated column into a normal column that keeps its current values. For a virtual column there are no values to keep, so it is rejected:

ALTER TABLE big ALTER COLUMN q2 DROP EXPRESSION;

ERROR:  ALTER TABLE / DROP EXPRESSION is not supported for virtual generated columns
DETAIL:  Column "q2" of relation "big" is a virtual generated column.

To convert, add a regular column, fill it with an UPDATE, then drop the virtual column.

Switching Between VIRTUAL and STORED

There is no ALTER COLUMN ... SET STORED or SET VIRTUAL in PostgreSQL 18. Both SET STORED and appending STORED to SET EXPRESSION are syntax errors. The supported path is to add a new column of the other kind, move application reads to it, and drop the old column.

Partitioned tables must also be consistent. Attaching a partition whose column is STORED to a parent whose column is VIRTUAL fails:

ERROR:  column "b" inherits from generated column of different kind
DETAIL:  Parent column is VIRTUAL, child column is STORED.

Step 9: Triggers and Logical Replication

Two less obvious areas behave differently from what you may assume.

Triggers See NULL

Row triggers do not see computed virtual values. In this test, the table has one virtual and one stored column with the same expression:

CREATE TABLE tg (
  a int,
  b int GENERATED ALWAYS AS (a * 2) VIRTUAL,
  c int GENERATED ALWAYS AS (a * 2) STORED
);
 
CREATE FUNCTION tgf() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN RAISE NOTICE 'BEFORE: b=%, c=%', NEW.b, NEW.c; RETURN NEW; END $$;
CREATE FUNCTION tgf2() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN RAISE NOTICE 'AFTER: b=%, c=%', NEW.b, NEW.c; RETURN NEW; END $$;
 
CREATE TRIGGER t1 BEFORE INSERT ON tg FOR EACH ROW EXECUTE FUNCTION tgf();
CREATE TRIGGER t2 AFTER INSERT ON tg FOR EACH ROW EXECUTE FUNCTION tgf2();
 
INSERT INTO tg (a) VALUES (5);

NOTICE:  BEFORE: b=<NULL>, c=<NULL>
NOTICE:  AFTER: b=<NULL>, c=10

The stored column is available in AFTER triggers; the virtual column is NULL in both. If an audit trigger copies NEW into a history table, it will record NULL for virtual columns. Compute the value in the trigger from the base columns, or use STORED.

Virtual Columns Are Not Replicated

PostgreSQL 18 lets publications include stored generated columns through the publish_generated_columns = stored option. Virtual columns cannot be published, and naming one in a column list fails:

CREATE PUBLICATION p2 FOR TABLE orders (id, total);

ERROR:  cannot use virtual generated column "total" in publication column list

On the subscriber, define the same virtual column on the target table so it computes the value locally.

VIRTUAL or STORED: How to Choose

Use this checklist when designing a new column.

Choose VIRTUAL when:

The expression is cheap (arithmetic, lower(), simple CASE, concatenation).
The table is large and you want to add or change the column without a rewrite.
Writes are frequent and you do not want every UPDATE to recompute and store the value.
You only need an index on it occasionally, and an expression index is acceptable.

Choose STORED when:

You need a unique constraint, a foreign key, or a direct index on the column.
The expression calls a user-defined function, or the column type is a domain or custom type.
The expression is expensive and read far more often than written, such as to_tsvector() over a long document.
Triggers or logical replication subscribers need the value.

A sensible starting point for most application schemas is: start with VIRTUAL for cheap derived fields, and reach for STORED only when one of the rules above forces you to.

Checking Your Existing Schema

To audit which generated columns you have and their kind across a database, query the catalog:

SELECT c.relname AS table_name,
       a.attname AS column_name,
       CASE a.attgenerated WHEN 'v' THEN 'VIRTUAL' WHEN 's' THEN 'STORED' END AS kind,
       pg_get_expr(d.adbin, d.adrelid) AS expression
FROM pg_attribute a
JOIN pg_class c ON c.oid = a.attrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_attrdef d ON d.adrelid = a.attrelid AND d.adnum = a.attnum
WHERE a.attgenerated <> ''
  AND NOT a.attisdropped
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY 1, 2;

Run this before and after an upgrade to PostgreSQL 18 to confirm that migrations which omitted the keyword produced the kind you intended. A GUI client makes this kind of catalog browsing faster; Chat2DB (opens in a new tab) shows generated column definitions in its table designer and lets you run the query above against several databases side by side.

FAQ

Is VIRTUAL really the default in PostgreSQL 18?

Yes. GENERATED ALWAYS AS (expr) without a keyword creates a virtual column in PostgreSQL 18, confirmed by attgenerated = 'v'. On PostgreSQL 17 and earlier the same statement is an error because STORED was required.

Are virtual generated columns slower to read?

The expression is evaluated for each row a query reads, so there is some CPU cost. For simple arithmetic or string functions it is usually negligible compared to reading the row. Measure with EXPLAIN (ANALYZE) on your own queries if the expression is complex.

Can I use a virtual column in WHERE, ORDER BY, and GROUP BY?

Yes. It behaves like a normal column in queries. The planner replaces it with the expression, and an expression index on the same expression can be used for filtering and ordering.

Does pg_dump preserve the kind?

Yes, but not in the way you might expect. In testing, pg_dump from PostgreSQL 18 wrote stored columns with the STORED keyword and virtual columns with no keyword at all, for example b integer GENERATED ALWAYS AS ((a * 2)). Restoring into PostgreSQL 18 recreates them as virtual because that is the default. Restoring that dump into an older server fails, because older versions require STORED.

Can a virtual column reference a stored generated column?

No. A generated column of either kind cannot reference another generated column. Reference the base columns directly.

PostgreSQL 18 B-tree Skip Scan Explained How to Build a SQL Data Dictionary