PostgreSQL生成列实现过程介绍

PostgreSQL中生成列是从其他列计算而来的特殊列。生成列与普通列不同,不是固定的值,而是又引用表中其他列的表达式决定的。生成列在SQL标准(ISO/IEC 9075)中引入,被主流RDBMS支持,PostgreSQL12开始支持生成列。

示例

下面首先创建一张表,用于演示PostgreSQL生成列:

CREATE TABLE Students (
 Id INTEGER PRIMARY KEY,
 FirstName VARCHAR(50),
 LastName VARCHAR(50),
 FullName VARCHAR(101) GENERATED ALWAYS AS (FirstName || ' ' || LastName) STORED
);

上面示例中最后一列是生成列,PostgreSQL生成列在定义中必须有GENERATED ALWAYS(通常其他关系型数据库为可选)。另外生成列结尾必须有STORED。这指定了生成列需要被存储(相对于virtual,下面有解释),其他关系型数据库这通常也是可选的(默认值为virtual)。

生成列的语法如下:

<column_name> <datatype> GENERATED ALWAYS AS(expression) [STORED|VIRTUAL]

在GENERATED ALWAYS AS (expression) Stored子句中,使用现有的列指定生成列表达式,为生成列计算值。

现在我们已经创建了带生成列的表,下面插入数据:

INSERT INTO Students (Id, FirstName, LastName) 
VALUES (0001, 'Lucy', 'Green');
INSERT INTO Students (Id, FirstName, LastName) 
VALUES (0002, 'Aziz', 'Ahmad');
INSERT INTO Students (Id, FirstName, LastName) 
VALUES (0003, 'Zohan', 'Ahuja');
INSERT INTO Students (Id, FirstName, LastName) 
VALUES (0004, 'Homer', 'Presley');
INSERT INTO Students (Id, FirstName, LastName) 
VALUES (0005, 'Sally', 'Smith');

注意,我们插入数据不包括生成列,现在查看数据:

SELECT * FROM Students;

结果为:

 id | firstname | lastname |   fullname    
----+-----------+----------+---------------
  1 | Lucy      | Green    | Lucy Green
  2 | Aziz      | Ahmad    | Aziz Ahmad
  3 | Zohan     | Ahuja    | Zohan Ahuja
  4 | Homer     | Presley  | Homer Presley
  5 | Sally     | Smith    | Sally Smith

我们看到生成列包含FirstName和LastName两列值的连接串。生成列表达式不仅为字符串连接,举例:一些场景中需要基于原价和折扣计算实际价格。

Stored vs Virtual

生成列可以为 stored 或 virtual. 两者的差异为:

  • STORED (aka 持久化): 列值被存储在表里. 当写(插入或更新)时存储列被重新计算,并像普通列一样占用存储空间。
  • VIRTUAL: 虚拟生成列不占用存储空间,仅当读时才计算。PostgreSQL (14) 仅支持stored 生成列。

限制说明

生成的列有许多限制。例如,生成表达式不能引用另一个生成的列。此外,它们只能使用不可变函数,不能以任何方式使用子查询或引用当前行以外的任何内容。具体包括:

  • 生成表达式只能使用不可变函数,不能以任何方式使用子查询或引用当前行以外的任何内容。
  • 生成表达式不能引用另一个生成的列。
  • 生成表达式不能引用系统列(表类除外)。
  • 生成的列不能有列默认值或标识定义。
  • 生成的列不能是分区键的一部分。
  • 外部表可以生成列
作者:梦想画家原文地址:https://blog.csdn.net/neweastsun/article/details/128639607

%s 个评论

要回复文章请先登录注册