PostgreSQL中生成的列

目录

介绍

背景

PostgreSQL 12

与SQL Server计算列比较

那么，生成的列与带有DEFAULT子句的常规列有何不同？

局限性

其他注意事项

PostgreSQL 11.x及更高版本

使用视图

使用普通列并使用触发器更新它

参考

---

介绍

生成列或计算列对列的作用就像视图对表的作用一样。PostgreSQL 对计算列使用术语“生成”列。该列的值始终是从表中的其他列计算或生成的。生成的列可以是虚拟的或存储的。虚拟列的值是在查询期间动态计算的，它们不会占用存储空间。已存储的列的值已预先计算并存储为表数据的一部分。

背景

像SQL Server和Oracle这样的现代数据库长期以来都有计算列，而PostgreSQL中缺少计算列使从其他数据库的迁移变得相当困难。本文尝试探索在不同版本的PostgreSQL中实现相同功能的不同方法。

PostgreSQL 12

PostgreSQL 12是世界上最受欢迎和功能最丰富的开源数据库的下一个主要发行版。PostgreSQL 12的稳定版本计划于2019年末发布，并支持生成列。

GENERATED ALWAYS AS子句用于创建生成列。用于定义生成列的表达式称为生成表达式。

-- PostgreSQL syntax
CREATE TABLE employee (
    ...,
    dob timestamp,
    age integer GENERATED ALWAYS AS _
                (date_part('year', CURRENT_TIME) - date_part('year', dob)) STORED
);

与SQL Server计算列比较

将此与SQL Server语法进行对比。SQL Server中没有特殊的关键字来声明计算列。您只需在AS子句后指定使我们成为计算列的表达式。

-- SQL Server Syntax
CREATE TABLE Employee (
    ...,
    dob datetime,
    age AS DATEDIFF(year,dob,GETDATE()) PERSISTED
);

请注意PERSISTED子句与PostgreSQL的STORED子句等效。SQL Server还支持非持久的计算列，只要不指定PERSISTED子句。但是PostgreSQL当前仅实现存储的生成列。

那么，生成的列与带有DEFAULT子句的常规列有何不同？

1. 如果未提供其他值，则在首次插入行时对列默认值进行一次评估。每当行发生更改且无法覆盖时，生成的列都会更新。
2. 可以在INSERTor UPDATE语句中为具有DEFAULT约束的列提供值。生成的列不能被赋值，它们总是被计算的。
3. 列的默认值不能引用表的其他列，而生成的列则专门用于引用该表。
4. 列默认值可以使用易变函数，例如random()或current_time，生成的列不能使用。

局限性

有几个限制适用于生成的列和涉及生成的列的表的定义：

1. 生成表达式只能使用不可变函数，而不能使用易变函数。在SQL Server术语中，它们分别称为确定性函数和非确定性函数。posgtres doc 2
2. 生成表达式不能使用子查询或以任何方式引用当前行以外的任何内容。posgtres doc 2
3. 生成表达式不能引用另一个生成的列。posgtres doc 2
4. 生成表达式不能引用系统列（tableoid除外）。posgtres doc 2
5. 生成的列不能具有列默认值或标识定义。posgtres doc 2
6. 生成的列不能是分区键的一部分。posgtres doc 2

其他注意事项

1. 外部表可以具有生成的列。posgtres doc 2
2. 对生成列的访问特权与其底层基础列分开维护。因此，您可以授予对角色的访问权限，以从生成的列中读取，但不能从基础基列中读取。posgtres doc 2
3. 从概念上讲，生成的列在BEFORE触发器运行后进行更新。因此，对BEFORE触发器中的基本列所做的更改将反映在生成的列中。但是生成的列本身无法在BEFORE触发器中访问。posgtres doc 2

PostgreSQL 11.x及更高版本

在撰写本文时，尚未发布PostgreSQL 12的稳定版本，并且在较旧版本上运行的工作负载可能仍需要此功能。在PostgreSQL 11.x和更早的版本中，有两种方法可以实现此目的：

使用视图

在这种方法中，表格没有'age'列。该视图用于需要'age'列的任何地方使用。

CREATE VIEW v_employee AS
SELECT dob, date_part('year', CURRENT_TIME) - date_part('year', dob) as age
FROM employee;

使用普通列并使用触发器更新它

在这种方法中，将'age'声明为规则整数列，并在inserts和updates 期间由trigger填充该列。

CREATE TABLE employee (     
    ...,     
    dob timestamp,     
    age integer 
);

CREATE OR REPLACE FUNCTION calc_age() RETURNS TRIGGER AS $body$
BEGIN
  NEW.age := date_part('year', CURRENT_TIME) - date_part('year', dob);
  RETURN NEW;
END;
$body$ LANGUAGE plpgsql;

这种方法的主要缺点是创建和维护视图/触发器以及记住在业务逻辑更改时进行更新的额外维护开销。在发布稳定版本后将数据库升级到PostgreSQL 12是减轻这些缺点的好主意，以消除维护成本。

参考

1. PostgreSQL 12: New Features and Enhancements
2. PostgreSQL: Documentation: 12: 5.3. Generated Columns