Generated Columns#

A generated column is computed from other columns. There are 2 kinds:

  1. Stored Generated Columns:

    • Computed when they are inserted / updated.

    • Occupy storage like normal columns.

  2. Virtual Generated Columns:

    • Computed when they are read.

    • Occupy no storage.

    • Not yet implemented in postgres.

To create a generated column, use the GENERATED ALWAYS AS clause in CREATE TABLE. The keyword STORED must be specified to choose the stored kind of generated column:

mydb=> CREATE TABLE roadtrip (
mydb(>   start_location   text,
mydb(>   start_time       timestamp,
mydb(>   end_location     text,
mydb(>   end_time         timestamp,
mydb(>   duration         interval GENERATED ALWAYS AS (end_time - start_time) STORED
mydb(> );
CREATE TABLE

mydb=> INSERT INTO roadtrip (start_location, start_time, end_location, end_time)
mydb->   VALUES ('Malindi', '2023-08-01 09:12', 'Changamwe', '2023-08-01 10:55');
INSERT 0 1

A generated column can’t be written to directly. But the keyword DEFAULT may be specified in INSERT and UPDATE commands:

mydb=> INSERT INTO roadtrip (start_location, start_time, end_location, end_time, duration)
mydb->   VALUES ('Nairobi', '2023-08-04 06:04', 'Naivasha', '2023 08-04 07:19', '1:15:00');
ERROR:  cannot insert a non-DEFAULT value into column "duration"
DETAIL:  Column "duration" is a generated column.

mydb=> INSERT INTO roadtrip (start_location, start_time, end_location, end_time, duration)
  VALUES ('Nairobi', '2023-08-04 06:04', 'Naivasha', '2023 08-04 07:19', DEFAULT);
INSERT 0 1
mydb=> SELECT * FROM roadtrip;
 start_location |     start_time      | end_location |      end_time       | duration 
----------------+---------------------+--------------+---------------------+----------
 Malindi        | 2023-08-01 09:12:00 | Changamwe    | 2023-08-01 10:55:00 | 01:43:00
 Nairobi        | 2023-08-04 06:04:00 | Naivasha     | 2023-08-04 07:19:00 | 01:15:00
(2 rows)

Column default

Generated column

Evaluated once, on insert

Updated whenever the row changes

May not refer to other columns

Usually refers to other columns

Can use volatile functions e.g. random()

Cannot use volatile functions

Rules#

  • The generation expression can only use immutable functions, and cannot use subqueries or reference anything other than the current row.

  • A generation expression cannot reference another generated column.

  • A generation expression cannot reference a system column, except tableoid.

  • A generated column cannot have a column default or identity definition.

  • A generated column cannot be part of a partition key.

  • Foreign tables can have generated columns (CREATE FOREIGN TABLE).

  • For inheritance:

    • If a parent column is a generated column, a child column must also be a generated column using the same expression. In the definition of the child column, leave off the GENERATED clause, as it will be copied from the parent.

    • In case of multiple inheritance, if one parent column is a generated column, then all parent columns must be generated columns and with the same expression.

    • If a parent column is not a generated column, a child column may be defined to be a generated column or not.

Generated columns maintain access privileges separately from their underlying base columns. You can arrange for a particular role to only read from a generated column but not from the underlying base columns.

Generated columns are updated after BEFORE triggers have run. Changes made to base columns in a BEFORE trigger will be reflected in generated columns. However, it is not allowed to access generated columns in BEFORE triggers.