PG中的生成列（Generated Columns）
👉 本文共约1827个字，系统预计阅读时间或需7分钟。
简介
在pg12中create table语句支持GENERATED column(生成列)。
生成列是一个特殊的列，它总是从其他列计算而来。因此说，它对于列就像视图对于表一样。生成列有两种:存储列和虚拟列。 存储生成列在写入(插入或更新)时计算，并且像普通列一样占用存储空间。虚拟生成列不占用存储空间并且在读取时进行计算。 如此看来，虚拟生成列类似于视图，存储生成列类似于物化视图(除了它总是自动更新之外)。 PostgreSQL目前只实现了存储生成列。
建立一个生成列，在 CREATE TABLE中使用 GENERATED ALWAYS AS 子句, 例如:
1 2 3 4 5 | CREATE TABLE people ( ..., height_cm numeric, height_in numeric GENERATED ALWAYS AS (height_cm / 2.54) STORED ); |
必须指定关键字 STORED 以选择存储类型的生成列。更多细节请参见 CREATE TABLE 。
生成列不能被直接写入. 在INSERT 或 UPDATE 命令中, 不能为生成列指定值, 但是可以指定关键字DEFAULT。
考虑列缺省情况和生成列之间的差异。 如果没有提供其他值，列缺省情况下在行被首次插入时计算一次;生成列则在行每次改变时进行更新，并且不能被取代。 列缺省情况下不能引用表的其他列；生成表达式通常会这样做。 列缺省情况下可以使用易失性函数，例如random()或引用当前时间函数; 而对于生成列这是不允许的。
生成列和涉及生成列的表的定义有几个限制:
- 生成表达式只能使用不可变函数，并且不能使用子查询或以任何方式引用当前行以外的任何内容。
- 生成表达式不能引用另一个生成列。
- 生成表达式不能引用系统表，除了
tableoid。 - 生成列不能具有列默认或标识定义。
- 生成列不能是分区键的一部分。
- 外部表可以有生成列. 更多细节请参见 CREATE FOREIGN TABLE .
- 对于继承:
- 如果父列是生成的列，则子列也必须也是使用相同的表达式生成的列。 在子列的定义中，不再使用
GENERATED子句，因为它将从父列复制过来。 - 在进行多重继承的情况下，如果一个父列是生成的列，那么所有父列都必须是生成的列，并且具有相同的表达式。
- 如果父列不是生成的列，子列可以定义是否为生成的列。
- 如果父列是生成的列，则子列也必须也是使用相同的表达式生成的列。 在子列的定义中，不再使用
使用生成列的其他注意事项。
本人提供Oracle、MySQL、PG等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
- 生成列保留着有别于其下层的基础列的访问权限。因此，可以对其进行排列以便于从生成列中读取特定的角色，而不是从下层基础列。
- 从概念上讲，生成列在
BEFORE触发器运行后更新。 因此，BEFORE触发器中的基础列所做的变更将反映在生成列中。 但相反，不允许访问BEFORE触发器中的生成列。
column_column_usage
PostgreSQL 12在information_schema下新增了column_column_usage系统表，该视图column_column_usage标识依赖于同一表中的另一个基本列生成的所有列。只包含当前启用的角色所拥有的表。column_column_usage系统表存储了生成列的有关信息。
| 名称 | 数据类型 | 描述 |
|---|---|---|
table_catalog | sql_identifier | 包含表的数据库的名称(总是当前数据库) |
table_schema | sql_identifier | 包含表的模式的名称 |
table_name | sql_identifier | 表的名称 |
column_name | sql_identifier | 生成列所依赖的基本列的名称 |
dependent_column | sql_identifier | 生成的列的名称 |
示例
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | postgres=# CREATE TABLE t_test_1(a int4, b int2, c int2, sum_abc int2 GENERATED ALWAYS AS (a+b+c) STORED ); CREATE TABLE postgres=# select * from information_schema.column_column_usage ; table_catalog | table_schema | table_name | column_name | dependent_column ---------------+--------------+------------+-------------+------------------ postgres | public | t_test_1 | a | sum_abc postgres | public | t_test_1 | b | sum_abc postgres | public | t_test_1 | c | sum_abc (3 rows) postgres=# \d t_test_1 Table "public.t_test_1" Column | Type | Collation | Nullable | Default ---------+----------+-----------+----------+------------------------------------------ a | integer | | | b | smallint | | | c | smallint | | | sum_abc | smallint | | | generated always as ((a + b + c)) stored postgres=# select * from t_test_1; a | b | c | sum_abc ---+---+---+--------- (0 rows) postgres=# insert into t_test_1 values(1,2,3); INSERT 0 1 postgres=# select * from t_test_1; a | b | c | sum_abc ---+---+---+--------- 1 | 2 | 3 | 6 (1 row) |
注意
- 在insert语句和update语句中，不能为GENERATED column(生成列)指定value，除了default关键字
- GENERATED column(生成列)的列值是物理存储在table中的
- GENERATED column(生成列)不能作为partition key
- 不能定义一个基于其他GENERATED column(生成列)的GENERATED column(生成列)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 | postgres=# CREATE TABLE gen1(c1 VARCHAR(10), c2 VARCHAR(10), c3 VARCHAR(20) GENERATED ALWAYS AS (c1 || c2) STORED) ; CREATE TABLE postgres=# \d+ gen1; Table 'public.gen1' Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+-----------------------+-----------+----------+-------------------------------------------------------+----------+--------------+------------- c1 | character varying(10) | | | | extended | | c2 | character varying(10) | | | | extended | | c3 | character varying(20) | | | generated always as (((c1::text || c2::text))) stored | extended | | Access method: heap postgres=# 注意点： 第一：在insert语句和update语句中，不能为GENERATED column(生成列)指定value，除了default关键字。 postgres=# INSERT INTO gen1 VALUES ('AB', 'CD', 'EF') ; ERROR: cannot insert into column 'c3' DETAIL: Column 'c3' is a generated column. postgres=# INSERT INTO gen1 VALUES ('AB', 'CD', DEFAULT) ; INSERT 0 1 postgres=# select * from gen1; c1 | c2 | c3 ----+----+------ AB | CD | ABCD (1 row) postgres=# 第二：GENERATED column(生成列)列值是物理存储在table中的 第三：GENERATED column(生成列)存放在information_schema.column_column_usage中 postgres=# select * from information_schema.column_column_usage; table_catalog | table_schema | table_name | column_name | dependent_column ---------------+--------------+------------+-------------+------------------ postgres | public | gen1 | c1 | c3 postgres | public | gen1 | c2 | c3 (2 rows) postgres=# postgres=# select attname, attgenerated FROM pg_attribute WHERE attname IN ('c1', 'c2', 'c3') and attrelid='gen1'::regclass; attname | attgenerated ---------+-------------- c1 | c2 | c3 | s (3 rows) postgres=# 上面的attgenerated='s'代表stored， 第四：GENERATED column(生成列)不能作为partition key postgres=# CREATE TABLE pgen1(c1 INT, c2 INT, c3 INT GENERATED ALWAYS AS (c1 + c2) STORED) PARTITION BY RANGE(c3) ; ERROR: cannot use generated column in partition key LINE 1: ...NERATED ALWAYS AS (c1 + c2) STORED) PARTITION BY RANGE(c3) ; ^ DETAIL: Column 'c3' is a generated column. postgres=# 第五：也不能定义一个基于其他GENERATED column(生成列)的GENERATED column(生成列)。 postgres=# CREATE TABLE gen2 (c1 INT, c2 INT GENERATED ALWAYS AS (c1*2) STORED, c3 INT GENERATED ALWAYS AS (c2*2) STORED) ; ERROR: cannot use generated column 'c2' in column generation expression LINE 1: ...AYS AS (c1*2) STORED, c3 INT GENERATED ALWAYS AS (c2*2) STOR... ^ DETAIL: A generated column cannot reference another generated column. postgres=# |
