# NULL、默认值与生成值

LLMS 索引： [llms.txt](/llms.txt)

---

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

## 4.3.1 “未知”“不存在”与空值语义 {#item-4-3-1}

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

| 原因 | 例子 | 应否合并为 NULL |
|---|---|---|
| 尚未发生 | draft 的 `placed_at` | 可以，状态给出原因 |
| 不适用 | captured payment 的 `failure_code` | 可以，status 给出原因 |
| 未知但应该知道 | 遗失的 provider timestamp | 往往应拒绝或单独标记 |
| 被删除/保密 | 用户请求隐藏字段 | 通常需要独立审计语义 |
| 空集合 | 订单没有 line | 关系中是零行，不是某列 NULL |

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

### 三值逻辑

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

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

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

```sql
WHERE status <> 'paid'
```

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

### CHECK 不会自动拒绝 NULL

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

```sql
price bigint CHECK (price > 0)
```

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

v1 的订单规则近似：

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

```sql
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 默认值、身份列与序列 {#item-4-3-2}

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

```sql
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 时，下面操作还不够：

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

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

```sql
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。脚本通过循环同时处理空/非空情况。

还要授权：

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

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

### 确定性种子

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

## 4.3.3 生成列与数据库派生事实 {#item-4-3-3}

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

```sql
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 的边界

| 机制 | 何时计算 | 可引用什么 | 是否存储 | 适合 |
|---|---|---|---|---|
| default | INSERT 缺省时一次 | 不能引用同一行其他列 | 是 | created_at、缺省配置 |
| stored generated | 每次写入行 | 当前行、immutable 表达式 | 是 | 高频读取的确定行内派生 |
| virtual generated | 读取时 | 受更严格表达式限制 | 否 | PG18 新能力，需版本门 |
| view expression | 查询时 | 可 join/aggregate | 否 | 跨行投影与接口 |
| materialized view | refresh 时 | 可 join/aggregate | 是 | 可接受陈旧的查询结果 |

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

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

```text
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 保证批准范围。验证同时检查：

```sql
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](https://www.postgresql.org/docs/18/ddl-constraints.html)
- [PostgreSQL 18：default value](https://www.postgresql.org/docs/18/ddl-default.html)
- [PostgreSQL 18：identity column](https://www.postgresql.org/docs/18/ddl-identity-columns.html)
- [PostgreSQL 18：sequence 函数](https://www.postgresql.org/docs/18/functions-sequence.html)
- [PostgreSQL 18：generated column](https://www.postgresql.org/docs/18/ddl-generated-columns.html)
- [PostgreSQL 18：JSON null 与 SQL NULL](https://www.postgresql.org/docs/18/datatype-json.html)

---

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