4.3 NULL、默认值与生成值

NULL、default、identity 和 generated column 都会让 INSERT 语句“少提供一些东西”，但它们表达四种不同事实：值缺席、缺省输入、键分配和行内派生。混用后最常见的结果是“不知道”被一个假默认覆盖，或者可以计算的值被多个写者分别维护。

4.3.1 “未知”“不存在”与空值语义

SQL NULL 不是空字符串、0、false，也不是一个能用 = 比较的普通值。它表示该列在这一行没有一个已知 SQL 值。缺席的业务原因可能不同：

只要不同原因会导致不同命令、权限、统计或展示，就不要把它们都压成无法区分的 NULL。

三值逻辑

涉及 NULL 的普通比较产生 UNKNOWN：

SELECT
    NULL = NULL,        -- NULL / UNKNOWN
    NULL <> 1,          -- NULL / UNKNOWN
    NULL IS NULL,       -- true
    NULL IS DISTINCT FROM NULL;  -- false

WHERE 只保留条件为 TRUE 的行，FALSE 与 UNKNOWN 都被过滤。因此：

WHERE status <> 'paid'

不会包含 status 为 NULL 的行。需要把 NULL 当一个可比较分支时，显式使用 IS NULL 或 IS [NOT] DISTINCT FROM。ch05 会在查询与并发语境中继续三值逻辑，本章先把它当模式设计合同。

CHECK 不会自动拒绝 NULL

PostgreSQL 的 CHECK 在表达式为 TRUE 或 NULL 时都视为通过。下面仍允许 NULL：

price bigint CHECK (price > 0)

若值必须存在，还要 NOT NULL。若 nullable 列与状态联动，应把所有分支写完，而不是指望 UNKNOWN 代替业务语义。

v1 的订单规则近似：

CHECK (
  (order_status = 'draft'
   AND placed_at IS NULL
   AND paid_at IS NULL
   AND cancelled_at IS NULL)
  OR
  (order_status = 'placed'
   AND placed_at IS NOT NULL
   AND paid_at IS NULL
   AND cancelled_at IS NULL)
  OR ...
)

这让每个状态的空值形状是封闭集合。payment 同样规定 declined 才有且必须有 failure_code，pending/captured 必须为 NULL。NULL 不再是“调用方忘了填也没关系”，而是由另一列解释的合法状态。

SQL NULL 与 JSON null

SELECT
    NULL::jsonb IS NULL AS sql_value_absent,
    'null'::jsonb IS NULL AS json_value_absent,
    '{"x":null}'::jsonb ? 'x' AS key_exists;

结果是 true、false、true：列值缺席、存在 JSON null、对象中存在一个值为 null 的 key 是三件事。若 API PATCH 还把 key 缺席解释为“不修改”，就有第四种命令语义。接口层必须显式映射，不能依赖驱动猜测。

NULL 设计清单

对每个 nullable column 写下：

1. 哪些业务状态允许 NULL；
2. NULL 表示尚未发生、不适用还是未知；
3. 谁能把它从 NULL 改为非 NULL，能否改回；
4. unique、join、aggregate 与 API 如何处理；
5. 是否需要伴随 reason/status 才能解释。

答不出来时优先 NOT NULL。PostgreSQL 官方也建议多数列应为 not null；允许 NULL 应是一项积极设计，而不是省略约束的默认。

4.3.2 默认值、身份列与序列

default 是“INSERT 省略该列或显式写 DEFAULT 时使用的表达式”，不是缺失业务信息的修复器：

created_at timestamptz(3)
           NOT NULL
           DEFAULT transaction_timestamp()

如果调用方显式传 NULL，default 不会替换它；NOT NULL 会拒绝。default 也不会持续维护列值，后续其他列变化时它不重新计算。

默认值的权威时钟

customer.created_at 与 product.created_at 表示数据库记录创建时间，因此可以由数据库默认产生。placed_at 与 provider occurred_at 表示业务/外部事件，必须由相应命令显式提交，不能用 default 掩盖事件时间遗失。

PostgreSQL 允许 default 使用 volatile 表达式。transaction_timestamp() 在整个事务内固定，适合同一事务产生一致的 recorded-at；clock_timestamp() 会在语句执行期间变化。选择哪一个是审计语义，不是风格偏好。

identity 是有生命周期的 default 机制

identity 列背后有隐式 sequence。INSERT 省略 ID 时等价于请求 sequence 的下一个值，但 sequence 状态与普通表事务不同：

• nextval() 的值即使事务回滚也不会归还；
• 并发会交错分配；
• sequence cache 和故障切换可能留下空洞；
• 手工 setval 可改变后续位置；
• identity 本身不替代 PK。

因此不应从连续 ID 推算“没有删除”、订单数量或严格提交先后。需要法定连续票号时，要单独建模分配、作废和审计，接受对应串行化成本。

数据迁移中的 sequence 对齐

从已有手工 bigint 添加 identity 时，下面操作还不够：

ALTER TABLE shop.sales_order
  ALTER COLUMN order_id
  ADD GENERATED BY DEFAULT AS IDENTITY;

新 sequence 通常从 1 开始，下一次自动 INSERT 会撞历史 PK。本章迁移对四张 identity 表执行：

SELECT pg_catalog.setval(
  pg_catalog.pg_get_serial_sequence(
    'shop.sales_order', 'order_id'
  ),
  (SELECT max(order_id) FROM shop.sales_order),
  true
);

空表要使用 setval(seq, 1, false)，这样下一次返回 1；非空表用 max 与 is_called=true，下一次返回 max+1。脚本通过循环同时处理空/非空情况。

还要授权：

GRANT USAGE, SELECT
ON ALL SEQUENCES IN SCHEMA shop
TO pg36_app;

table INSERT 和 sequence USAGE 是不同权限。verify-v1.sql用 has_sequence_privilege 检查，反例脚本再以真实 app role 插入，防止“owner 测得通，应用却报 permission denied”。

确定性种子

seed-v1.sql先 TRUNCATE ... RESTART IDENTITY，显式插入固定 ID，再把 sequence 对齐到最大值。它只适用于隔离教学数据；生产数据库不应为了重放 fixture 重置 identity。BY DEFAULT 让这种导入可行，但也意味着运行权限设计要阻止不受信调用方自行选号。

4.3.3 生成列与数据库派生事实

生成列是“由同一行其他列永远计算出来”的事实。本章订单行：

line_total_minor bigint
GENERATED ALWAYS AS (
  unit_price_minor * quantity::bigint
) STORED

应用不能直接给它赋值；base column 插入或更新时，PostgreSQL 重新计算。本章反例显式提交 line_total_minor=1，应得到 SQLSTATE 428C9，证明不存在第二个写者。

default、generated、view 的边界

PostgreSQL 14–17 只支持 stored generated column；PG18 增加 virtual，并把省略 kind 的默认行为改为 virtual。本书共同基线因此始终显式写 STORED，不依赖版本默认。

generation expression 只能使用 immutable 函数，不能含 subquery，也不能引用另一 generated column；它适合 unit_price_minor * quantity，不适合：

sum(all lines of this order)
current product price
captured payments
now()

这些值依赖其他行、其他表或时间。订单 subtotal 继续放在 shop_api.order_summary view 中；若未来缓存，必须有独立一致性与刷新合同。

存储不是免费

stored generated column占行空间，并在 base field 更新时增加计算与 WAL/写入。它可能被索引，读取也不必重复计算；是否值得由读写比例和行宽证明。本例是教学上的小而确定派生：金额整数相乘便宜，结果被 summary 使用，并用边界约束防溢出。

注意生成列 attnotnull 不会因为表达式看起来非空而自动变 true。本例由 unit_price_minor 和 quantity 的 NOT NULL 保证结果非 NULL，再由 bounds CHECK 保证批准范围。验证同时检查：

a.attgenerated = 's'
line_total_minor = unit_price_minor * quantity::bigint

什么时候不保存派生值

优先查询时计算，除非至少有一项证据：

• 表达式昂贵且读远多于写；
• 需要对派生值建立索引；
• 派生值是经批准的写时快照，而非随源事实变化；
• 性能测试证明存储收益超过行宽与写放大。

不要以“以后查询方便”为理由复制 subtotal、captured amount 到 order 头。每多一个存储副本，就要回答谁在并发、失败与恢复后维护一致。

本节验收

• 每个 nullable 列都有状态解释，CHECK 分支不会被 UNKNOWN 穿透；
• 能区分 SQL NULL、JSON null、JSON key 缺席与 PATCH 不修改；
• default 只用于权威可缺省输入，不覆盖外部事件事实；
• identity sequence 在迁移与 seed 后都对齐，app 拥有精确权限；
• generated column 只维护 immutable 行内派生，跨行聚合仍在 view；
• PG18 virtual generated 没有误写成 PG14–18 共同行为。

参考资料

• PostgreSQL 18：约束与 NULL
• PostgreSQL 18：default value
• PostgreSQL 18：identity column
• PostgreSQL 18：sequence 函数
• PostgreSQL 18：generated column
• PostgreSQL 18：JSON null 与 SQL NULL

上一节：标识、状态与半结构化数据 · 返回本章目录 · 下一节：用约束表达不变量 · 查看全书目录 · 查看索引中心