PostgreSQL / 系统课时
生成列与表达式
生成列与表达式
生成列适合保存能够由同一行其他字段稳定推导出的值。本课将用订单明细示例创建存储生成列,并说明表达式为什么必须不可变、何时值得建立索引,以及哪些数据不应交给生成列维护。
学习目标
- 能使用
GENERATED ALWAYS AS (...) STORED定义生成列 - 能判断表达式是否满足不可变要求
- 能评估生成列的存储、写入和索引成本
生成列解决什么问题
订单金额、标准化编码、长度或分类标记经常可以由基础字段计算。如果每条查询都重复表达式,容易出现口径不一致;如果由应用手工写入,又可能在更新源字段时漏掉同步。
生成列把“计算规则”放进表结构。应用只写基础字段,PostgreSQL 在插入或更新行时计算结果。生成列不是新的业务事实,而是由当前行确定的派生值,因此源字段仍然是唯一事实来源。
创建存储生成列
下面创建订单明细表:normalized_sku 负责统一商品编码格式,line_total 负责计算折后金额。示例可在独立测试库中直接执行。
DROP TABLE IF EXISTS order_line;
CREATE TABLE order_line (
id bigint PRIMARY KEY,
sku text NOT NULL,
quantity integer NOT NULL CHECK (quantity > 0),
unit_price numeric(10, 2) NOT NULL CHECK (unit_price >= 0),
discount_rate numeric(5, 4) NOT NULL DEFAULT 0
CHECK (discount_rate >= 0 AND discount_rate <= 1),
normalized_sku text
GENERATED ALWAYS AS (lower(trim(sku))) STORED,
line_total numeric(12, 2)
GENERATED ALWAYS AS (
round(quantity * unit_price * (1 - discount_rate), 2)
) STORED
);
INSERT INTO order_line (
id, sku, quantity, unit_price, discount_rate
) VALUES (
1, ' BOOK-POSTGRESQL ', 3, 199.90, 0.1500
);
SELECT id, normalized_sku, line_total
FROM order_line
ORDER BY id;预期输出如下。客户端的列宽和对齐方式可能不同,但值应一致。
id | normalized_sku | line_total
----+------------------+------------
1 | book-postgresql | 509.75插入时不能为生成列提供普通值;更新 sku、quantity、unit_price 或 discount_rate 后,相关生成列会自动重新计算。这样可以避免应用代码和数据库规则出现两套计算口径。
不可变表达式与使用边界
生成表达式必须只依赖当前行,并且使用不可变函数。所谓不可变,是指相同输入始终得到相同输出,不依赖当前时间、随机数、数据库配置或其他表的数据。
算术运算、lower、trim 这类确定性处理通常适合生成列;CURRENT_TIMESTAMP、random()、子查询和读取其他表的函数不适合。表达式也不能引用另一生成列,否则依赖链会让计算顺序和变更规则变得不明确。
自定义函数只有在真实满足确定性时才应声明为 IMMUTABLE。为了绕过校验而错误标记函数,会把外部状态伪装成稳定结果,最终产生难以解释的数据差异。
生成列可以像普通列一样建立索引。例如,经常按标准化商品编码检索时,可执行 CREATE INDEX idx_order_line_normalized_sku ON order_line (normalized_sku);,再用 EXPLAIN 验证查询是否选择该索引。
生成列会增加行存储空间,并在源字段变化时增加计算和索引维护成本。它更适合“读取频繁、表达式稳定、结果需要统一口径”的派生值;只在偶尔查询中使用的简单表达式,直接计算往往更轻量。
跨行聚合、当前时间、外部汇率和其他表字段都不应放入生成列。此类结果应通过查询、视图、物化视图、触发器或专门的汇总流程维护,并明确刷新时机。
本课总结
- 生成列把同一行内的稳定派生规则固化到表结构中,应用只维护基础字段
- 生成表达式必须依赖当前行并使用真正不可变的函数
- 是否使用生成列,应同时评估读取收益、写入成本、存储空间和索引维护
下一课预告
下一课将从单表派生规则转向整体数据模型,比较规范化与反规范化在一致性、查询效率和变更复杂度上的取舍。