六月 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)
总结
使用生成列在表中自动进行计算。