作者
digoal
日期
2019-03-30
标签
PostgreSQL , Generated columns
背景
Generated columns为SQL标准的特性，用于创建虚拟列，虚拟列为表达式（必须使用immutable function或operator作为表达式），表达式内可以引用表内的其他列。虚拟列只能读，不能写。
表达式的计算可以指定写时计算，或读时计算。
1、写时计算，需要将表达式的值存下来。
2、读时计算，写入时不需要存表达式的值，在读时计算表达式的值。
PostgreSQL 12支持Generated columns特性。
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=fc22b6623b6b3bab3cb057ccd282c2bfad1a0b30
```
Generated columns
This is an SQL-standard feature that allows creating columns that are
computed from expressions rather than assigned, similar to a view or
materialized view but on a column basis.
This implements one kind of generated column: stored (computed on
write). Another kind, virtual (computed on read), is planned for the
future, and some room is left for it.
Reviewed-by: Michael Paquier michael@paquier.xyz
Reviewed-by: Pavel Stehule pavel.stehule@gmail.com
Discussion: https://www.postgresql.org/message-id/flat/b151f851-4019-bdb1-699e-ebab07d2f40a@2ndquadrant.com
```
Generated columns 例子
GENERATED ALWAYS AS ( generation_expr ) STORED |
GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ] |
语法讲解
```
GENERATED ALWAYS AS ( generation_expr ) STORED
This clause creates the column as a generated column.
The column cannot be written to,
and when read it will be computed from the specified expression.
The keyword STORED is required to signify that the column
will be computed on write and will be stored on disk. default.
The generation expression can refer to other columns in the table,
but not other generated columns.
Any functions and operators used must be immutable.
References to other tables are not allowed.
```
例子，vcol作为虚拟列，结果为info指定的大写内容。
postgres=# \df+ upper
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+-------+------------------+---------------------+------+------------+----------+----------+----------+-------------------+----------+-------------+----------------------
pg_catalog | upper | anyelement | anyrange | func | immutable | safe | postgres | invoker | | internal | range_upper | upper bound of range
pg_catalog | upper | text | text | func | immutable | safe | postgres | invoker | | internal | upper | uppercase
(2 rows)
```
postgres=# create table test (id int, info text,
vcol text GENERATED ALWAYS AS (upper(info)) stored);
postgres=# \set VERBOSITY verbose
postgres=# insert into test (id,info,vcol) values (1,'TEST','test');
2019-03-30 22:27:59.336 CST [57211] ERROR: cannot insert into column "vcol"
2019-03-30 22:27:59.336 CST [57211] DETAIL: Column "vcol" is a generated column.
2019-03-30 22:27:59.336 CST [57211] STATEMENT: insert into test (id,info,vcol) values (1,'TEST','test');
ERROR: 42601: cannot insert into column "vcol"
DETAIL: Column "vcol" is a generated column.
LOCATION: rewriteTargetListIU, rewriteHandler.c:827
如下，vcol结果为info字段的大写。
postgres=# insert into test (id,info) values (1,'test');
INSERT 0 1
postgres=# select * from test;
id | info | vcol
----+------+------
1 | test | TEST
(1 row)
```
当前虚拟列仅支持写时计算，还没有支持读时计算。
This implements one kind of generated column: stored (computed on
write). Another kind, virtual (computed on read), is planned for the
future, and some room is left for it.
参考
《PostgreSQL 10 新特性 - identity column (serial, 自增)》
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=fc22b6623b6b3bab3cb057ccd282c2bfad1a0b30
https://www.postgresql.org/docs/devel/sql-createtable.html
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
