比十万多一点

毕方

0%

PostgreSQL 12支持 Generated Columns 特性

发表于 更新于 分类于 Disqus：

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。

使用心得：

  • 所谓“使用表达式和操作符必须是immutable属性”就是不能是动态的操作，比如表达式中有函数，它会认为是动态的。
  • 更新相关的字段，generated column 字段中的表达式包含这些字段的话，该generated column字段会被更新。

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_score) VALUES(1,80,70,100);
psql: ERROR:  cannot insert into column "sum"
DETAIL:  Column "sum" is a generated column.

mydb=> UPDATE score SET sum_score=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 支持创建索引。