5.3. Generated Columns
A generated column is a special column that is always computed from other columns. Thus, it is for columns what a view is for tables. There are two kinds of generated columns: stored and virtual. A stored generated column is computed when it is written (inserted or updated) and occupies storage as if it were a normal column. A virtual generated column occupies no storage and is computed when it is read. Thus, a virtual generated column is similar to a view and a stored generated column is similar to a materialized view (except that it is always updated automatically). PostgreSQL currently implements only stored generated columns.
To create a generated column, use theGENERATED ALWAYS AS clause inCREATE TABLE, for example:
CREATE TABLE people ( ..., height_cm numeric, height_in numericGENERATED ALWAYS AS (height_cm / 2.54) STORED); The keywordSTORED must be specified to choose the stored kind of generated column. SeeCREATE TABLE for more details.
A generated column cannot be written to directly. InINSERT orUPDATE commands, a value cannot be specified for a generated column, but the keywordDEFAULT may be specified.
Consider the differences between a column with a default and a generated column. The column default is evaluated once when the row is first inserted if no other value was provided; a generated column is updated whenever the row changes and cannot be overridden. A column default may not refer to other columns of the table; a generation expression would normally do so. A column default can use volatile functions, for examplerandom() or functions referring to the current time; this is not allowed for generated columns.
Several restrictions apply to the definition of generated columns and tables involving generated columns:
The generation expression can only use immutable functions and cannot use subqueries or reference anything other than the current row in any way.
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 an identity definition.
A generated column cannot be part of a partition key.
Foreign tables can have generated columns. SeeCREATE FOREIGN TABLE for details.
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
GENERATEDclause, 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.
Additional considerations apply to the use of generated columns.
Generated columns maintain access privileges separately from their underlying base columns. So, it is possible to arrange it so that a particular role can read from a generated column but not from the underlying base columns.
Generated columns are, conceptually, updated after
BEFOREtriggers have run. Therefore, changes made to base columns in aBEFOREtrigger will be reflected in generated columns. But conversely, it is not allowed to access generated columns inBEFOREtriggers.Generated columns are skipped for logical replication.