Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to use generated columns in PostgreSQL 12". In daily operation, I believe many people have doubts about how to use generated columns in PostgreSQL 12. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the doubts about "how to use generated columns in PostgreSQL 12"! Next, please follow the editor to study!

In other databases, generated columns is called computed column (calculated columns) / virtual column (virtual columns) and so on.

Brief introduction

In PG 11 or previous versions, generated columns is not supported:

Testdb=# drop table if exists tasking generatedmakers: 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, ^

The values of counter columns are obtained by the addition of N1 and N2, while the values of link columns are spliced by C1 and C2.

In PG 12, generated columns is supported

Testdb=# drop table if exists tweak generatedmakers: 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 1testdb=# testdb=# select n2) values; INSERT 0 1testdb=# testdb=# select * from tactile generatedgeneratecoll; N1 | N2 | C1 | c2 | counter | link-+-1 | 1 | C1 | c2 | 2 | c1c2 (1 row)

The value of the generated column is calculated from the value of the expression, or null if null:

Testdb=# insert into t_generated_col; INSERT 0 1testdb=# select * from tactile generatedgeneratecoll; N1 | N2 | C1 | counter | link-+-1 | 1 | C1 | c2 | 2 | c1c2 1 | C1 | | (2 rows)

The generated column cannot be update:

Testdb=# update t_generated_col set counter = 10 psql: column "counter" can only be updated to DEFAULTDETAIL: Column "counter" is a generated column.

You can create an index on it:

Testdb=# create index idx_t_generated_col_counter on t_generated_col (counter); CREATE INDEX

When executing a query, it is no different from a normal column:

^ testdb=# insert into t_generated_col (N1 10000testdb=# testdb=# explain select N2) select x Magne0 from generate_series (110000) x TX 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) The study on "how to use generated columns in PostgreSQL 12" is over. I hope I can solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.