PostgreSQL 生成列(Generated Columns)教程

PostgreSQL 12 增加新的特性——生成列(Generated Columns)，也就是计算列。在之前版本也可以实现，但需要定义函数和触发器，利用该功能可以更容易使用并可以提升性能。

生成列是给表指定计算列，其数据可以根据其他列数据自动生成，当原数据更新时其自动更新。

PostgreSQL 12+ 生成列介绍

在PostgreSQL 12+中生成列作为内置功能，可以在创建或修改表时指定列作为生成列，指定其内容通过表达式自动填充，可以为简单基于其他进行数学运算，或更复杂的函数。其优势包括：

• 无需在插入或修改操作时需要应用代码负责生成数据，生成列作为计算列自动实现。
• 在频繁的select语句避免计算处理时间。因为插入或修改时计算列已经填充，因此查询无需临时计算，但需要额外空间进行存储。
• 因为更新原数据时，生成列数据自动更新，因此计算列的值正确性有了保障。

在PostgreSQL 12+中仅STORED类型生成列有效。其他数据库类型，VIRTUAL类型的生成列也有效，该实现更类似于视图方式实现，在数据返回时进行实时计算。这里不讨论两者的优劣，业务未来版本会支持两种方式。

生成列示例

下面创建表演示生成列。我们指定计算列自动计算利润，依据销售价格和采购价格，公示为：

profit = ((sale_price - purchase_price) * quantity_sold)

创建交易表 transactions：

CREATE TABLE public.transactions (
    transactions_sid serial primary key, 
    transaction_date timestamp with time zone DEFAULT now() NOT NULL, 
    product_name character varying NOT NULL, 
    purchase_price double precision NOT NULL, 
    sale_price double precision NOT NULL, 
    quantity_sold integer NOT NULL, 
    profit double precision NOT NULL GENERATED ALWAYS AS  ((sale_price - purchase_price) * quantity_sold) STORED 
);

插入示例数据，假设交易表存储咖啡店的交易信息：

INSERT INTO public.transactions (product_name, purchase_price, sale_price, quantity_sold) VALUES ('House Blend Coffee', 5, 11.99, 1);
 
INSERT INTO public.transactions (product_name, purchase_price, sale_price, quantity_sold) VALUES ('French Roast Coffee', 6, 12.99, 4);
 
INSERT INTO public.transactions (product_name, purchase_price, sale_price, quantity_sold) VALUES ('BULK: House Blend Coffee, 10LB', 40, 100, 6);
  
SELECT * FROM public.transactions;

返回结果：

transactions_sid	transaction_date	product_name	purchase_price	sale_price	quantity_sold	profit
1	2021-09-13 17:06:12	House Blend Coffee	5.0	11.99	1	6.99
2	2021-09-13 17:06:12	French Roast Coffee	6.0	12.99	4	27.96
3	2021-09-13 17:06:12	BULK: House Blend Coffee, 10LB	40.0	100.0	6	360.0

我们更新数据验证是否会自动变化：

UPDATE public.transactions SET sale_price = 95 WHERE transactions_sid = 3;
  
SELECT * FROM public.transactions;

返回结果：

transactions_sid	transaction_date	product_name	purchase_price	sale_price	quantity_sold	profit
1	2021-09-13 17:06:12	House Blend Coffee	5.0	11.99	1	6.99
2	2021-09-13 17:06:12	French Roast Coffee	6.0	12.99	4	27.96
3	2021-09-13 17:06:12	BULK: House Blend Coffee, 10LB	40.0	95.0	6	330.0

于期望一致，这种机制确保计算列值的正确性，无需额外的应用程序代码负责实现。

注意：计算列（生成列）不能被直接插入或更新，否则会返回错误。

总结

本文介绍了PostgreSQL 12+ 生成列，可以方便实现计算列。如何使用之前版本，则需要自定义触发器予以实现。