PostgreSQL 12: 支持 Generated Columns 特性

PostgreSQL 12 一个给力SQL特性是增加了对 Generated Columns 的支持,这个特性并不陌生,MySQL 已经支持这个特性。

这个特性对分析类场景比较有用,本文简单测试下 PostgreSQL 的这个特性。

发行说明

Add support for generated columns (Peter Eisentraut)

The content of generated columns are computed from expressions (including references to other columns in the same table) rather than being specified by INSERT or UPDATE commands.

补丁说明

1
2
3
4
5
6
7
8
9
10
11
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.

Discussion: https://www.postgresql.org/message-id/flat/-4019-bdb1-699e-@2ndquadrant.com

generated column 列的值是根据其它列表达式计算而得,和基于表的物化视图有些类似。

目前仅支持 stored (computed on write), virtual (computed on read) 暂不支持,以后版本会支持。

Generated columns 语法

GENERATED ALWAYS AS ( generation_expr ) STORED

generated column 说明如下:

  • STORED: 表示 Generated columns 类型为 “computed on write”, 数据存储到磁盘上。
  • generation_expr: 只能引用本表的非 generated column 字段,不可以引用其它表的字段,使用的表达式和操作符必须是 immutable 属性。
  • generated column 字段只读: 不支持 INSRET 和 UPDATE。

Generated columns 演示

创建测试表 score 并插入一条测试,如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mydb=> CREATE TABLE score(stuid int4, chinese int2, math int2, sum_score int2 GENERATED ALWAYS AS (chinese+math) STORED );
CREATE TABLE

mydb=> \d score
Table "pguser.score"
Column | Type | Collation | Nullable | Default
-----------+----------+-----------+----------+---------------------------------------------
stuid | integer | | |
chinese | smallint | | |
math | smallint | | |
sum_score | smallint | | | generated always as (chinese + math) stored


mydb=> INSERT INTO score(stuid,chinese,math) VALUES(1,90,95);
INSERT 0 1

备注: 表 socre 的 sum_score 为 generated column。

验证 sum_score 列的数据,如下:

1
2
3
4
5
mydb=> SELECT * FROM score;
stuid | chinese | math | sum_score
-------+---------+------+-----------
1 | 90 | 95 | 185
(1 row)

Generated columns 不支持 INSERT 和 UPDATE,如下:

1
2
3
4
5
6
7
mydb=> INSERT INTO score(stuid,chinese,math,sum) VALUES(1,80,70,100);
psql: ERROR: cannot insert into column "sum"
DETAIL: Column "sum" is a generated column.

mydb=> UPDATE score SET sum=100 WHERE stuid=1;
psql: ERROR: column "sum" can only be updated to DEFAULT
DETAIL: Column "sum" is a generated column.

Generated columns 支持创建索引,如下:

1
2
mydb=> CREATE INDEX idx_score_sum ON score USING BTREE(sum);
CREATE INDEX

总结

本文简单演示了 Generated columns 的使用,注意如下:

  • Generated columns 有两种类型, Stored (computed on write) 和 Virtual (computed on read),目前仅支持 stored 类型,数据存储到磁盘上。
  • Generated columns 只能引用本表的非 generated column 字段,不可以引用其它表的字段。
  • Generated columns 使用的表达式和操作符必须是 Immutable 属性。
  • generated columns 字段只读,不支持 INSRET 和 UPDATE。
  • generated columns 支持创建索引。

参考

最后推荐和张文升共同编写的《PostgreSQL实战》,本书基于PostgreSQL 10 编写,共18章,重点介绍SQL高级特性、并行查询、分区表、物理复制、逻辑复制、备份恢复、高可用、性能优化、PostGIS等,涵盖大量实战用例!

购买链接:https://item.jd.com/12405774.html

PostgreSQL实战
感谢支持!
0%