Generated column
A generated column
is a special column which is always computed from other columns.
Generated columns were introduced in PostgreSQL 12.
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.
From PostgreSQL 13, a generated column can be converted into a normal column using the ALTER TABLE ... ALTER COLUMN ... DROP EXPRESSION
command.
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.
Change history
- PostgreSQL 13
- generated columns can be converted to normal columns (commit f595117e)
- PostgreSQL 12
- added (commit fc22b662)
Examples
Basic example of a table with a generated column, here for pre-calculating an archaic temperature conversion:
postgres=# CREATE TABLE temperature ( celsius SMALLINT NOT NULL, fahrenheit SMALLINT NOT NULL GENERATED ALWAYS AS ((celsius * 9/5) + 32) STORED ); CREATE TABLE postgres=# \d temperature Table "public.temperature" Column | Type | Collation | Nullable | Default ------------+----------+-----------+----------+----------------------------------------------------- celsius | smallint | | not null | fahrenheit | smallint | | not null | generated always as ((celsius * 9 / 5 + 32)) stored
Inserting data:
postgres=# INSERT INTO temperature VALUES (0), (100); INSERT 0 2 postgres=# SELECT * FROM temperature; celsius | fahrenheit ---------+------------ 0 | 32 100 | 212 (2 rows)
It is not possible to directly insert a value into a generated column:
postgres=# INSERT INTO temperature VALUES (0, 32); ERROR: cannot insert a non-DEFAULT value into column "fahrenheit" DETAIL: Column "fahrenheit" is a generated column.
However the DEFAULT
keyword can be used as a placeholder value:
postgres=# INSERT INTO temperature VALUES (0, DEFAULT); INSERT 0 1
Converting the fahrenheit
column to an ordinary column (PostgreSQL 13 and later):
postgres=# ALTER TABLE temperature ALTER COLUMN fahrenheit DROP EXPRESSION; ALTER TABLE postgres=# \d temperature Table "public.temperature" Column | Type | Collation | Nullable | Default ------------+----------+-----------+----------+--------- celsius | smallint | | not null | fahrenheit | smallint | | not null |
Attempting to convert a non-generated column to an ordinary column:
postgres=# ALTER TABLE temperature ALTER COLUMN celsius DROP EXPRESSION; ERROR: column "celsius" of relation "temperature" is not a stored generated column
Part of the entry in pg_attribute
for the temperature
table defined above:
postgres=# SELECT attname, attnum, attgenerated FROM pg_attribute WHERE attrelid='temperature'::regclass AND attnum > 0; attname | attnum | attgenerated ------------+--------+-------------- celsius | 1 | fahrenheit | 2 | s (2 rows)
The generated column expression is stored in pg_attrdef
:
postgres=# SELECT pg_get_expr(adbin, adrelid, true) FROM pg_attrdef WHERE adrelid = 'temperature'::regclass; pg_get_expr ------------------------ (celsius * 9 / 5 + 32) (1 row)
References
- PostgreSQL documentation: Generated columns