怎么使用PostgreSQL 12中的generated columns

这篇文章主要介绍"怎么使用PostgreSQL 12中的generated columns"，在日常操作中，相信很多人在怎么使用PostgreSQL 12中的generated columns问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答"怎么使用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, skippingDROP TABLEtestdb=# create table t_generated_coltestdb-# (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, skippingDROP TABLEtestdb=# create table t_generated_coltestdb-# (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 TABLEtestdb=# insert into  t_generated_col(n1,n2,c1,c2) values(1,1,'c1','c2');INSERT 0 1testdb=# 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 1testdb=# 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 DEFAULTDETAIL:  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 10000testdb=# 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)

到此，关于"怎么使用PostgreSQL 12中的generated columns"的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注网站，小编会继续努力为大家带来更多实用的文章！