Generated column

A special column which is always computed from other columns

A generated column is a special column which is always computed from other columns.

Generated colums were introduced in PostgreSQL 12 (commit fc22b662).

Functionality

As of PostgreSQL 12, generated columns can only be of type STORED. Commit fc22b662 indicates the other type of generated column, VIRTUAL, may be added at a future date.

Functionality to convert a generated column into a normal column will be added in PostgreSQL 13 via the ALTER TABLE ... ALTER COLUMN ... DROP EXPRESSION command (commit f595117e).

Implementation

The column attgenerated has been added to pg_attribute to indicate whether the column is generated; s indicates the column is of type STORED, otherwise the value is empty and the column is not a generated one.

Foreign data wrapper (FDW) support

Foreign tables can be defined with generated columns, and the generated value will be supplied to the underlying foreign data wrapper. The FDW should ensure the value is stored on the foreign server and that it can be subsequently retrieved, however there is no guarantee that the retrieved value will not have been changed on the foreign server.

Categories

DDL, Storage