本节简单介绍了PostgreSQL 12中的generated columns.在其他数据库中,generated columns又被成为计算列(calculated columns)/虚拟列(virtual columns)等. **简介** 在PG 11或以前的版本中,generated columns是不支持的: ``` testdb=# drop table if exists t_generated_col; NOTICE: table "t_generated_col" does not exist, skipping DROP TABLE testdb=# create table t_generated_col testdb-# (n1 int, testdb(# n2 int, testdb(# c1 varchar(10), testdb(# c2 varchar(10), testdb(# counter int generated always as (n1 + n2) stored, testdb(# link varchar(20) generated always as (c1 || c2) stored); ERROR: syntax error at or near "(" LINE 6: counter int generated always as (n1 + n2) stored, ^ ``` counter列的值由n1和n2相加而得,而link列的值有c1和c2拼接而得. 在PG 12中,可以支持generated columns ``` testdb=# drop table if exists t_generated_col; psql: NOTICE: table "t_generated_col" does not exist, skipping DROP TABLE testdb=# create table t_generated_col testdb-# (n1 int, testdb(# n2 int, testdb(# c1 varchar(10), testdb(# c2 varchar(10), testdb(# counter int generated always as (n1 + n2) stored, testdb(# link varchar(20) generated always as (c1 || c2) stored); CREATE TABLE testdb=# insert into t_generated_col(n1,n2,c1,c2) values(1,1,'c1','c2'); INSERT 0 1 testdb=# testdb=# select * from t_generated_col; n1 | n2 | c1 | c2 | counter | link ----+----+----+----+---------+------ 1 | 1 | c1 | c2 | 2 | c1c2 (1 row) ``` 生成列的值由表达式的值计算而得,如为null则为null: ``` testdb=# insert into t_generated_col(n1,n2,c1,c2) values(1,null,'c1',null); INSERT 0 1 testdb=# select * from t_generated_col; n1 | n2 | c1 | c2 | counter | link ----+----+----+----+---------+------ 1 | 1 | c1 | c2 | 2 | c1c2 1 | | c1 | | | (2 rows) ``` 生成列不能被update: ``` testdb=# update t_generated_col set counter = 10; psql: ERROR: column "counter" can only be updated to DEFAULT DETAIL: Column "counter" is a generated column. ``` 可以创建在其上创建index: ``` testdb=# create index idx_t_generated_col_counter on t_generated_col(counter); CREATE INDEX ``` 执行查询时,跟普通列没有什么区别: ``` ^ testdb=# insert into t_generated_col(n1,n2) select x,0 from generate_series(1,10000) x; INSERT 0 10000 testdb=# testdb=# explain select * from t_generated_col where counter = 1000; QUERY PLAN ---------------------------------------------------------------------------------------------------- Index Scan using idx_t_generated_col_counter on t_generated_col (cost=0.29..8.30 rows=1 width=23) Index Cond: (counter = 1000) (2 rows) ``` **参考资料** [Generated columns in PostgreSQL 12](https://www.2ndquadrant.com/en/blog/generated-columns-in-postgresql-12/)