六月 4, 2024
摘要：在本教程中，您将了解 PostgreSQL 的生成列，其值是自动从其他列计算出来的。
目录
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)
总结
使用生成列在表中自动进行计算。