SQL Server 的计算列，其实 PostgreSQL 早就有了！-51CTO.COM

PostgreSQL 生成列简介

在 PostgreSQL 中，生成列是一种特殊类型的列，其值是根据表达式或其他列的值自动计算出来的。

生成列在 SQL Server 中称为计算列，在 Oracle 中称为虚拟列。

有两种类型的生成列：

• 存储型：存储型生成列在插入或更新时计算，并占用存储空间。
• 虚拟型：虚拟型生成列在读取时计算，不占用存储空间。

虚拟型生成列类似于视图，而存储型生成列类似于物化视图。与物化视图不同，PostgreSQL 会自动更新存储型生成列的数据。

注意：PostgreSQL 目前仅实现了存储型生成列。

定义生成列

通常，你可以在创建表时，使用下面的语法定义生成列：

CREATE TABLE table_name(
   ...,
   colum_name type GENERATED ALWAYS AS (expression ) STORED | VIRTUAL,
   ...
);

在此语法中：

• column_name：指定生成列的名称。

• type：指定列的数据类型。

• expression：提供一个返回计算列值的表达式。

• STORED关键字：表示生成列的数据物理存储在表中。

• VIRTUAL关键字：表示生成列的数据是在查询时计算的，而不是物理存储的。

要给一个表添加一个生成列，可以使用 ALTER TABLE … ADD COLUMN 语句：

ALTER TABLE table_name
ADD COLUMN column_name type GENERATED ALWAYS AS (expression) STORED;

在为生成列定义一个表达式时，请确保它满足以下要求：

• 表达式只能使用不可变函数，不能涉及子查询或引用当前行以外的任何内容。例如，表达式不能使用 CURRENT_TIMESTAMP 函数。

• 表达式不能引用另一个生成列或系统列，但tableoid除外。

生成列不能带有默认值或标识定义。此外，它不能是分区键的一部分。

PostgreSQL 生成列示例

让我们来探索一些使用生成列的示例。

1) 拼接列值

首先，创建一个名为contacts的新表：

CREATE TABLE contacts(
   id SERIAL PRIMARY KEY,
   first_name VARCHAR(50) NOT NULL,
   last_name VARCHAR(50) NOT NULL,
   full_name VARCHAR(101) GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED,
   email VARCHAR(300) UNIQUE
);

第二步，将行插入contacts表。full_name列的值将从first_name和last_name列的值自动更新：

INSERT INTO contacts(first_name, last_name, email)
VALUES
   ('John', 'Doe', 'john.doe@rockdata.net'),
   ('Jane', 'Doe', 'jane.doe@rockdata.net')
RETURNING *;

输出：

id | first_name | last_name | full_name |              email
----+------------+-----------+-----------+---------------------------------
  1 | John       | Doe       | John Doe  | john.doe@rockdata.net
  2 | Jane       | Doe       | Jane Doe  | jane.doe@rockdata.net
(2 rows)

2) 计算净价

首先，创建一个名为products的表，用于存储产品信息：

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    list_price DECIMAL(10, 2) NOT NULL,
    tax DECIMAL(5, 2) DEFAULT 0,
    discount DECIMAL(5, 2) DEFAULT 0,
    net_price DECIMAL(10, 2) GENERATED ALWAYS AS ((list_price + (list_price * tax / 100)) - (list_price * discount / 100)) STORED
);

在products表中，net_price列是一个生成列，其值是根据价目表价格、税费和折扣按以下公式计算的：

list_price = list_price + (list_price * tax / 100)) - (list_price * discount / 100)

然后，将行插入到products表中：

INSERT INTO products (name, list_price, tax, discount)
VALUES
    ('A', 100.00, 10.00, 5.00),
    ('B', 50.00, 8.00, 0.00),
    ('C', 120.00, 12.50, 10.00)
RETURNING *;

输出：

id | name | list_price |  tax  | discount | net_price
----+------+------------+-------+----------+-----------
  1 | A    |     100.00 | 10.00 |     5.00 |    105.00
  2 | B    |      50.00 |  8.00 |     0.00 |     54.00
  3 | C    |     120.00 | 12.50 |    10.00 |    123.00
(3 rows)

总结

使用生成列在表中自动进行计算。