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.<\/p>\n
<\/p>\n
We begin with a simple table containing two columns:<\/p>\n
\npostgres=# create table t1 ( a int, b text );\nCREATE TABLE\npostgres=# insert into t1 values (1,'aaa');\nINSERT 0 1\npostgres=# select * from t1;\n a | b \n---+-----\n 1 | aaa\n(1 row)\n\npostgres=# \n<\/pre>\nA generated column is not a “real” column because it’s value is computed:<\/p>\n
\npostgres=# alter table t1 add column c int generated always as (a*2) stored;\nALTER TABLE\npostgres=# select * from t1;\n a | b | c \n---+-----+---\n 1 | aaa | 2\n postgres=# d t1\n Table \"public.t1\"\n Column | Type | Collation | Nullable | Default \n--------+---------+-----------+----------+------------------------------------\n a | integer | | | \n b | text | | | \n c | integer | | | generated always as (a * 2) stored\n<\/pre>\nThe 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.<\/p>\n
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:<\/p>\n
\npostgres=# alter table t1 add column d int generated always as (3*2) stored;\nALTER TABLE\npostgres=# d t1\n Table \"public.t1\"\n Column | Type | Collation | Nullable | Default \n--------+---------+-----------+----------+------------------------------------\n a | integer | | | \n b | text | | | \n c | integer | | | generated always as (a * 2) stored\n d | integer | | | generated always as (3 * 2) stored\n<\/pre>\nReferencing columns of other tables is not possible and it is not possible to reference another generated columns:<\/p>\n
\npostgres=# alter table t1 add column d int generated always as (c*2) stored;\nERROR: cannot use generated column \"c\" in column generation expression\nDETAIL: A generated column cannot reference another generated column.\n<\/pre>\nDirectly updating such a column does of course not work as well:<\/p>\n
\npostgres=# update t1 set d=5;\npsql: ERROR: column \"d\" can only be updated to DEFAULT\nDETAIL: Column \"d\" is a generated column.\npostgres=# update t1 set d=default;\nUPDATE 1\n<\/pre>\nWhat will happen when we create a generated column that uses a volatile function?<\/p>\n
\npostgres=# alter table t1 add column e int generated always as (random()) stored;\npsql: ERROR: generation expression is not immutable\npostgres=# \n<\/pre>\nDoes not work as well. Only immutable expressions can be used here. That would work:<\/p>\n
\npostgres=# alter table t1 add column e text generated always as (md5(b)) stored;\nALTER TABLE\npostgres=# d t1\n Table \"public.t1\"\n Column | Type | Collation | Nullable | Default \n--------+---------+-----------+----------+-------------------------------------\n a | integer | | | \n b | text | | | \n c | integer | | | generated always as (a * 2) stored\n d | integer | | | generated always as (3 * 2) stored\n e | text | | | generated always as (md5(b)) stored\n<\/pre>\n