PostgreSQL中的生成列

Shameel

5.00/5 (2投票s)

2019 年 9 月 19 日

CPOL

4分钟阅读

41145

本文介绍了在不同版本的 PostgreSQL 中设置生成（计算）列的各种方法。

引言

生成列或计算列对于列来说，就像视图对于表一样。PostgreSQL 使用“Generated”（生成）列这个术语来指代计算列。该列的值始终根据表中的其他列进行计算或生成。生成列可以是虚拟的，也可以是存储的。虚拟列的值在查询时即时计算，不占用存储空间。存储列的值会预先计算并作为表数据的一部分存储起来。

背景

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 子句的普通列有何不同？

如果未提供其他值，列的默认值在行首次插入时计算一次；而生成列会在行更改时更新，且无法被覆盖。
可以为 DEFAULT 约束的列在 INSERT 或 UPDATE 语句中提供值。生成列无法提供值，它们始终是计算生成的。
列的默认值不能引用表中的其他列，而生成列的目的就是如此。
列的默认值可以使用易变函数，例如 random() 或 current_time，而生成列不能。

限制和约束

对生成列和涉及生成列的表的定义有几项限制

生成表达式只能使用不可变函数，而不能使用易变函数。用 SQL Server 的术语来说，它们分别称为确定性函数和非确定性函数。PostgreSQL 文档²
生成表达式不能使用子查询，也不能以任何方式引用当前行以外的内容。PostgreSQL 文档²
生成表达式不能引用另一个生成列。PostgreSQL 文档²
生成表达式不能引用系统列，除了 tableoid。PostgreSQL 文档²
生成列不能有列默认值或标识定义。PostgreSQL 文档²
生成列不能作为分区键的一部分。PostgreSQL 文档²

其他注意事项

外部表可以包含生成列。PostgreSQL 文档²
生成列的访问权限与它们的基础列是分开维护的。因此，您可以授予角色读取生成列的权限，但不能授予读取基础列的权限。PostgreSQL 文档²
生成列在概念上是在 BEFORE 触发器运行后更新的。因此，在 BEFORE 触发器中对基础列所做的更改将反映在生成列中。但在 BEFORE 触发器中无法访问生成列本身。PostgreSQL 文档²

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' 被声明为一个常规整数列，并且一个 trigger 在 insert 和 update 时填充该列。

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：初始版本
版本 2：添加了限制和约束以及附加考虑事项部分