PostgreSQL / 系统课时

生成列与表达式

生成列与表达式

生成列适合保存能够由同一行其他字段稳定推导出的值。本课将用订单明细示例创建存储生成列,并说明表达式为什么必须不可变、何时值得建立索引,以及哪些数据不应交给生成列维护。

学习目标

  • 能使用 GENERATED ALWAYS AS (...) STORED 定义生成列
  • 能判断表达式是否满足不可变要求
  • 能评估生成列的存储、写入和索引成本

生成列解决什么问题

订单金额、标准化编码、长度或分类标记经常可以由基础字段计算。如果每条查询都重复表达式,容易出现口径不一致;如果由应用手工写入,又可能在更新源字段时漏掉同步。

生成列把“计算规则”放进表结构。应用只写基础字段,PostgreSQL 在插入或更新行时计算结果。生成列不是新的业务事实,而是由当前行确定的派生值,因此源字段仍然是唯一事实来源。

创建存储生成列

下面创建订单明细表:normalized_sku 负责统一商品编码格式,line_total 负责计算折后金额。示例可在独立测试库中直接执行。

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

预期输出如下。客户端的列宽和对齐方式可能不同,但值应一致。

text
 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 验证查询是否选择该索引。

生成列会增加行存储空间,并在源字段变化时增加计算和索引维护成本。它更适合“读取频繁、表达式稳定、结果需要统一口径”的派生值;只在偶尔查询中使用的简单表达式,直接计算往往更轻量。

跨行聚合、当前时间、外部汇率和其他表字段都不应放入生成列。此类结果应通过查询、视图、物化视图、触发器或专门的汇总流程维护,并明确刷新时机。

本课总结

  • 生成列把同一行内的稳定派生规则固化到表结构中,应用只维护基础字段
  • 生成表达式必须依赖当前行并使用真正不可变的函数
  • 是否使用生成列,应同时评估读取收益、写入成本、存储空间和索引维护

下一课预告

下一课将从单表派生规则转向整体数据模型,比较规范化与反规范化在一致性、查询效率和变更复杂度上的取舍。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

生成列与表达式

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通