Skip to main content
 首页 » 数据库

PostgreSQL 生成列(Generated Columns)教程

2022年07月19日151Terrylee

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+ 生成列,可以方便实现计算列。如何使用之前版本,则需要自定义触发器予以实现。


本文参考链接:https://blog.csdn.net/neweastsun/article/details/120273779
阅读延展