Contents

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 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

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)

Categories

DDL, Storage