Infrastructure at your Service

PostgreSQL 12 will finally bring a feature other database systems already have for quite some time: Generated columns. What exactly is that and how does is look like in PostgreSQL? As usual, lets start with a simple test setup.

We begin with a simple table containing two columns:

postgres=# create table t1 ( a int, b text );
CREATE TABLE
postgres=# insert into t1 values (1,'aaa');
INSERT 0 1
postgres=# select * from t1;
 a |  b  
---+-----
 1 | aaa
(1 row)

postgres=# 

A generated column is not a “real” column because it’s value is computed:

postgres=# alter table t1 add column c int generated always as (a*2) stored;
ALTER TABLE
postgres=# select * from t1;
 a |  b  | c 
---+-----+---
 1 | aaa | 2
 postgres=# \d t1
                              Table "public.t1"
 Column |  Type   | Collation | Nullable |              Default               
--------+---------+-----------+----------+------------------------------------
 a      | integer |           |          | 
 b      | text    |           |          | 
 c      | integer |           |          | generated always as (a * 2) stored

The keyword “stored” means that the column is stored on disk. In a future version there will probably also be a “virtual” keyword which instructs PostgreSQL not to store the data on disk but always compute it when it is read rather then written.

What you can see here as well is, that you can refer other columns of the same table for the computation of the generated column. But this is not a requirement:

postgres=# alter table t1 add column d int generated always as (3*2) stored;
ALTER TABLE
postgres=# \d t1
                              Table "public.t1"
 Column |  Type   | Collation | Nullable |              Default               
--------+---------+-----------+----------+------------------------------------
 a      | integer |           |          | 
 b      | text    |           |          | 
 c      | integer |           |          | generated always as (a * 2) stored
 d      | integer |           |          | generated always as (3 * 2) stored

Referencing columns of other tables is not possible and it is not possible to reference another generated columns:

postgres=# alter table t1 add column d int generated always as (c*2) stored;
ERROR:  cannot use generated column "c" in column generation expression
DETAIL:  A generated column cannot reference another generated column.

Directly updating such a column does of course not work as well:

postgres=# update t1 set d=5;
psql: ERROR:  column "d" can only be updated to DEFAULT
DETAIL:  Column "d" is a generated column.
postgres=# update t1 set d=default;
UPDATE 1

What will happen when we create a generated column that uses a volatile function?

postgres=# alter table t1 add column e int generated always as (random()) stored;
psql: ERROR:  generation expression is not immutable
postgres=# 

Does not work as well. Only immutable expressions can be used here. That would work:

postgres=# alter table t1 add column e text generated always as (md5(b)) stored;
ALTER TABLE
postgres=# \d t1
                               Table "public.t1"
 Column |  Type   | Collation | Nullable |               Default               
--------+---------+-----------+----------+-------------------------------------
 a      | integer |           |          | 
 b      | text    |           |          | 
 c      | integer |           |          | generated always as (a * 2) stored
 d      | integer |           |          | generated always as (3 * 2) stored
 e      | text    |           |          | generated always as (md5(b)) stored

Nice feature. Documentation is here

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Daniel Westermann
Daniel Westermann

Principal Consultant & Technology Leader Open Infrastructure