PostgreSQL 生成列

在PostgreSQL中，生成列（Generated Columns）是一种特殊的列，其值是通过表达式从同一表中的其他列自动计算得出的。生成列的值不能直接插入或更新，而是由数据库系统根据定义的表达式自动生成。生成列可以是存储的（Stored）或虚拟的（Virtual），但目前PostgreSQL仅支持存储的生成列。

生成列的基本概念

生成列的主要用途是简化数据管理，避免冗余计算。例如，如果你有一个包含价格和数量的表，你可以创建一个生成列来自动计算总价，而不需要每次查询时手动计算。

生成列的定义语法如下：

sql
column_name data_type GENERATED ALWAYS AS (expression) STORED

column_name：生成列的名称。
data_type：生成列的数据类型。
expression：用于计算生成列值的表达式。
STORED：表示生成列的值是存储的，而不是虚拟的。

备注

PostgreSQL目前仅支持存储的生成列，这意味着生成列的值会实际存储在表中，而不是在查询时动态计算。

生成列的示例

假设我们有一个存储订单信息的表 orders，其中包含 price（价格）和 quantity（数量）两列。我们可以创建一个生成列 total_price 来自动计算每个订单的总价。

sql
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    price NUMERIC(10, 2),
    quantity INTEGER,
    total_price NUMERIC(10, 2) GENERATED ALWAYS AS (price * quantity) STORED
);

在这个例子中，total_price 列的值会根据 price 和 quantity 的值自动计算并存储。

插入数据

让我们插入一些数据并查看生成列的效果：

sql
INSERT INTO orders (price, quantity) VALUES (10.50, 3);
INSERT INTO orders (price, quantity) VALUES (25.00, 2);

查询数据

现在，我们可以查询 orders 表，看看 total_price 列的值是否被正确计算：

sql
SELECT * FROM orders;

输出结果如下：

id	price	quantity	total_price
1	10.50	3	31.50
2	25.00	2	50.00

可以看到，total_price 列的值是根据 price 和 quantity 的值自动计算得出的。

生成列的实际应用场景

生成列在许多实际场景中非常有用，以下是一些常见的应用场景：

1. 计算总价

如前所述，生成列可以用于自动计算订单的总价、发票的总金额等。

2. 格式化数据

生成列可以用于格式化数据。例如，你可以创建一个生成列来将名字和姓氏组合成一个完整的名字：

sql
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    full_name TEXT GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED
);

3. 计算日期差

生成列可以用于计算日期差。例如，你可以创建一个生成列来计算订单的交付时间：

sql
CREATE TABLE deliveries (
    id SERIAL PRIMARY KEY,
    order_date DATE,
    delivery_date DATE,
    delivery_days INTEGER GENERATED ALWAYS AS (delivery_date - order_date) STORED
);

生成列的注意事项

不可更新：生成列的值不能直接更新，只能通过修改依赖的列来间接更新。
存储开销：由于生成列的值是存储的，因此会增加表的存储空间。
表达式限制：生成列的表达式必须是确定性的，不能包含随机函数或依赖于当前时间的函数。

警告

生成列的表达式必须是确定性的，不能包含随机函数或依赖于当前时间的函数。否则，PostgreSQL会报错。

总结

生成列是PostgreSQL中一个非常有用的功能，它可以帮助我们自动计算并存储基于其他列的值。通过生成列，我们可以简化数据管理，避免冗余计算，并提高查询性能。虽然PostgreSQL目前仅支持存储的生成列，但在许多实际场景中，它仍然非常有用。

附加资源

练习

创建一个包含生成列的表，用于存储学生的成绩信息。生成列应自动计算学生的总分。
尝试在生成列中使用不同的表达式，例如字符串拼接或日期计算。
思考生成列与视图的区别，并讨论它们各自的优缺点。

通过以上练习，你将更深入地理解生成列的概念及其应用场景。

生成列的基本概念​

生成列的示例​

插入数据​

查询数据​

生成列的实际应用场景​

1. 计算总价​

2. 格式化数据​

3. 计算日期差​

生成列的注意事项​

总结​

附加资源​

练习​