跳到主要内容

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 列的值会根据 pricequantity 的值自动计算并存储。

插入数据

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

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;

输出结果如下:

idpricequantitytotal_price
110.50331.50
225.00250.00

可以看到,total_price 列的值是根据 pricequantity 的值自动计算得出的。

生成列的实际应用场景

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

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
);

生成列的注意事项

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

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

总结

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

附加资源

练习

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

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