PostgreSQL 12 Generated ColumnsPostgreSQL 12 introduces the generated columns, columns that are automatically computed depending on a generation expression.
The usage of generated columns is quite simple and can be summarized as follows:
- the column must be annotated with the
GENERATED ALWAYS AS (...) STOREDinstruction;
- the expression in parentheses must use only
IMMUTABLEfunctions and cannot use subqueries.
For more specific constraints, see the official documentation.
STOREDclause because at the moment PostgreSQL supports only that kind of columns: a
STOREDgenerated column is saved on disk storage as a normal column would be, the only difference is that you cannot modify it autonomously, the database will compute it for you.
You can think of a stored generated column as a trade-off between a table with a trigger and a materialized view. When the
VIRTUAL(as opposed to
STORED) will be implemented, the column will take no space at all and will be computed on each column access, something similar as a view.
An example of not-generated columnLet’s see generated columns in action: consider an ordinary table with a dependency between the
agecolumn and the
birthdayone, since the former can be computed from the values in the latter column:
Let’s see how much space does it occupy to have such table filled with one million of rows:
testdb=> CREATE TABLE people( name text, birthday date, age int ); testdb=> WITH year AS ( SELECT ( random() * 100 )::int % 70 AS y ) INSERT INTO people( name, age, birthday ) SELECT 'Person ' || v, y, current_date - ( y * 365 ) FROM generate_series(1, 1000000 ) v, year;
testdb=> SELECT pg_relation_size( 'people' ); pg_relation_size ------------------ 52183040
An example with generated columnsIn order to create a similar table where
ageis automatically computed.
Since the column must use an
IMMUTABLEfunction, the first step is to abstract the computation into a function:
Then it is possible to create the table using the function as generation method:
testdb=> CREATE OR REPLACE FUNCTION f_person_age( birthday date ) RETURNS int AS $CODE$ BEGIN RETURN extract( year FROM CURRENT_DATE ) - extract( year FROM birthday ) + 1; END $CODE$ LANGUAGE plpgsql IMMUTABLE;
If the table is filled in a similar way, the space occupied is the same:
testdb=> CREATE TABLE people_gc_stored ( name text, birthday date, age int GENERATED ALWAYS AS ( f_person_age( birthday ) ) STORED );
Why using a function in the generated column? Because if we place the real expression we got an error at creation time:
testdb=> INSERT INTO people_gc_stored( name, birthday ) SELECT 'Person ' || v, current_date - v FROM generate_series(1, 1000000 ) v; testdb=> SELECT pg_relation_size( 'people_gc_stored' ); pg_relation_size ------------------ 52183040
testdb=> CREATE TABLE people_gc_stored ( name text, birthday date, age int GENERATED ALWAYS AS ( extract( year FROM CURRENT_DATE ) - extract( year FROM birthday ) + 1 ) STORED ); ERROR: generation expression is not immutable
Writing the generated columnAs already written, the generated column is not writable once it has been computed:
testdb=> UPDATE people_gc_stored SET age = 40; ERROR: column "age" can only be updated to DEFAULT DETAIL: Column "age" is a generated column.
Querying the generated columnThe generated column works and behaves as a normal column, that is access can be restricted or granted on such column:
testdb=# REVOKE ALL ON people_gc_stored FROM public; testdb=# GRANT SELECT( name, age ) ON people_gc_stored TO harry;
harryhas access only on columns
age, the user cannot see the dependency column:
On the other hand, giving access only on
testdb=> SELECT * FROM luca.people_gc_stored LIMIT 5; ERROR: permission denied for table people_gc_stored testdb=> SELECT min( age ), max( age ) FROM luca.people_gc_stored; min | max -----|------ 1 | 2740 (1 row) testdb=> SELECT min( birthday ), max( birthday ) FROM luca.people_gc_stored; ERROR: permission denied for table people_gc_stored
birthdaycolumn does not automatically provide access on
testdb=# REVOKE SELECT ON people_gc_stored FROM harry; testdb=# GRANT SELECT( name, birthday ) ON people_gc_stored TO harry;
testdb=> SELECT min( birthday ), max( birthday ) FROM luca.people_gc_stored; min | max ---------------|------------ 0720-12-07 BC | 2019-11-03 (1 row) testdb=> SELECT min( age ), max( age ) FROM luca.people_gc_stored; ERROR: permission denied for table people_gc_stored