PostgreSQL 18: Virtual generated columns

This is about another feature which was committed for PostgreSQL 18: Virtual generated columns. Generated columns are available already, but they need to be “STORED”. This means the result of an expression is stored to disk and the result is computed while writing, but you cannot write to that column explicitly. Before looking at the virtual generated columns lets do a quick refresh on “STORED” generated columns.

Consider a simple a table like this:

postgres=# create table t ( a int, b int );
CREATE TABLE
postgres=# insert into t values (1,99);
INSERT 0 1

If you want to automatically calculate the sum of “a” and “b” you could use a generated column for this:

postgres=# alter table t add column c int generated always as (a+b) stored; 
ALTER TABLE
postgres=# select * from t;
 a | b  |  c  
---+----+-----
 1 | 99 | 100
(1 row)

As mentioned above, you are not allowed to directly write to that column:

postgres=# update t set c = -1 where a = 1;
ERROR:  column "c" can only be updated to DEFAULT
DETAIL:  Column "c" is a generated column.

A downside of this type of generated column is, that the result of the expression is actually stored to disk and obviously consumes space.

With the addition of “virtual generated columns” this concept is somehow flipped: The expression of a virtual column is computed while reading, so there is no storage on disk. On the other side you spend the work for computing the expression also while reading, and not while writing as it is done with “STORED” virtual columns:

postgres=# alter table t add column d int generated always as (a-b) virtual; 
ALTER TABLE
postgres=# select * from t;
 a | b  |  c  |  d  
---+----+-----+-----
 1 | 99 | 100 | -98
(1 row)

The default, from PostgreSQL 18 on, will be virtual, which is also reflected here:

postgres=# \d t
                               Table "public.t"
 Column |  Type   | Collation | Nullable |              Default               
--------+---------+-----------+----------+------------------------------------
 a      | integer |           |          | 
 b      | integer |           |          | 
 c      | integer |           |          | generated always as (a + b) stored
 d      | integer |           |          | generated always as (a - b)

Nice, thanks to everyone involved.