# 金额、文本与时间

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

---

类型选择不是从 PostgreSQL 类型表中挑一个“看起来像”的名字。先写单位、允许范围、比较规则、输入输出协议和舍入时点，类型才有答案。本节先关闭三个最容易产生静默歧义的合同：金额的单位、文本的相等性、事件时间的瞬间语义。

## 4.1.1 整数、`numeric` 与金额精度 {#item-4-1-1}

“精确金额”至少包含币种、最小单位、范围和舍入规则。`88.00` 这个字面量没有告诉数据库它是人民币元、美元，还是精度为两位的比率。

PostgreSQL 的主要选择是：

| 表达 | 精确性 | 适用条件 | 主要风险 |
|---|---|---|---|
| `bigint` 最小单位 | 十进制合同下精确、固定 8 字节 | 单位固定，乘加范围可证明 | 忘记单位；乘法溢出；多币种 scale 不同 |
| `numeric(p,s)` | 任意精度十进制，按声明 scale 强制 | 计量、汇率、多币种或法规要求小数 | 超 scale 输入会舍入；运算/存储成本高于整数 |
| unconstrained `numeric` | 精确但不限制 scale | 中间计算或输入暂存 | 不能表达业务精度；还能保存 `NaN`/Infinity |
| `real` / `double precision` | 二进制近似 | 科学计算、容忍误差的测量 | 十进制金额不能保证精确相等 |
| PostgreSQL `money` | 固定小数的货币格式 | 少数受控、locale 固定场景 | 输入输出受 `lc_monetary` 影响，币种语义仍不完整 |

`numeric` 是正确工具，但“金额一律 numeric”仍然太粗。声明 `numeric(12,2)` 时，超出两位的小数会先被舍入，而不是天然拒绝：

```sql
CREATE TEMP TABLE amount_probe (v numeric(12,2));
INSERT INTO amount_probe VALUES (1.239);
SELECT v FROM amount_probe;  -- 1.24
```

如果业务要求“客户端不得提交超过两位”，应在 API/域层先拒绝，并在迁移中证明可表示性；不能把数据库舍入误读为输入验证。unconstrained `numeric` 还允许特殊值。尤其 PostgreSQL 为了可排序，把 `NaN` 视为等于自身且大于普通数，因此 `CHECK (amount > 0)` 不是排除 `NaN` 的可靠方法。

### 本案例为什么用整数“分”

`pg36_shop` v1 明确限定单币种人民币：

```text
currency_code = CNY
storage unit   = fen
100 fen        = 1 yuan
refund         = a separate future fact
```

于是：

```sql
current_unit_price_minor bigint
unit_price_minor         bigint
amount_minor             bigint
line_total_minor         bigint
```

`88.00` 元迁移为 `8800` 分，`39.90 × 2` 精确得到 `7980` 分。列名带 `_minor`，避免调用方把整数误当元；每个订单聚合又携带 `currency_code`。订单行与支付通过 `(order_id, currency_code)` 复合外键引用订单，不能在同一订单下悄悄混入另一币种。

这项选择不是普遍定律。若一个系统同时支持 JPY、CNY、KWD，最小单位的小数位并不相同；若保存汇率、利率或高精度计量，`numeric(p,s)` 往往更清楚。正确问题是“这一列的量纲与运算合同是什么”，不是“哪种类型更快”。

### 迁移必须先证明，而不是直接 cast

从 numeric 转 bigint 有一个危险细节：`1.5::numeric::bigint` 会舍入成 `2`。因此 [`migrate-v0-to-v1.sql`](/labs/ch04/migrate-v0-to-v1.sql)先检查：

```sql
value * 100 = trunc(value * 100)
```

它验证“以分表示时没有残余”，又允许 `88.000` 这种只有尾随零的输入；只检查 `scale(value) <= 2` 会错误拒绝后者。迁移还显式拒绝 numeric 特殊值和越界值，然后才做：

```sql
(value * 100)::bigint
```

列约束把商品/订单行单价限制在 `0..10^12` 分、quantity 限制在 `1..10^6`，从而让生成乘积最多 `10^18`，仍在 signed bigint 的范围内。即使极端输入先在生成表达式中溢出，PostgreSQL 也会失败而不是环绕；边界的价值是让批准范围可读、可测试。

负金额也不是自动等于退款。payment v1 要求正数；退款需要自己的 provider reference、状态与生命周期，将在业务范围扩展时另建事实。用 `-amount` 复用 payment 会把两个不同事件压进一列符号。

### 金额验收

```sql
SELECT
    order_id,
    item_subtotal_minor,
    captured_amount_minor,
    currency_code
FROM shop_api.order_summary
WHERE order_id = 1001;
```

预期两项金额均为 `16780`、币种为 `CNY`。再把 v0 某价格改成 `88.001` 后运行迁移，脚本应以状态 `3` 返回，错误为：

```text
product price cannot be represented as bounded integer minor units
```

整个事务回滚，v0 view 仍存在，v1 version marker 不存在。这才叫无损迁移门。

## 4.1.2 `text`、排序规则与大小写语义 {#item-4-1-2}

`text` 解决的是可变长字符串存储，不会自动解决“两个字符串是否代表同一业务身份”。相等、排序、大小写转换和正则字符分类都会受 collation 影响。

PostgreSQL 中 `text`、`varchar` 与无长度限制的 `varchar` 都能保存变长字符串；`varchar(n)` 额外强制字符数上限。不要为了“数据库优化”给所有列随意加 `varchar(255)`。只有协议或业务确实存在上限时，长度才是不变量；否则 `text` 加针对语义的 `CHECK` 更直接。

### 把机器标识与人类文本分开

v1 对两类文本采用不同策略：

| 类别 | 例子 | 语义 |
|---|---|---|
| 机器业务键 | `CUST-ALICE`、`SKU-MUG`、`ORD-...` | ASCII、大小写固定、字节稳定 |
| 人类展示文本 | display/product name | Unicode，不把自然语言排序写进身份 |

机器键使用 `COLLATE "C"` 的正则检查，例如：

```sql
CHECK (sku COLLATE "C" ~ '^SKU-[A-Z0-9-]+$')
```

`C` 采用传统字节/ASCII 行为，适合这里刻意受限的标识。自然语言列表若需要中文拼音、德语或重音规则，应在查询/列上选择经批准的 ICU collation；不能让某台 OS 的默认 locale 偶然决定全局业务键。

### “大小写不敏感”不是一个完整需求

至少要回答：

- 只覆盖 ASCII，还是完整 Unicode？
- 重音、全半角、Unicode 不同正规形是否等价？
- 比较等价是否也要影响排序、LIKE 与正则？
- collation provider/版本升级后怎样重建受影响索引？
- API 返回原始写法，还是规范写法？

v1 的 email 只是教学范围内的小写 ASCII 联系地址：

```sql
CHECK (
  email = lower(email COLLATE "C")
  AND email COLLATE "C"
      ~ '^[a-z0-9][a-z0-9._+%-]*@[a-z0-9][a-z0-9.-]*$'
)
```

这不是 RFC 完整 email 验证，更不是全球通用账户身份算法。它只确保样例系统的所有写入口先规范化，并让原有 exact unique constraint 足以拒绝重复。`UpperCase@example.test` 会触发 `customer_email_canonical`。

真正的 Unicode case-insensitive 唯一性可以考虑 ICU nondeterministic collation、`citext`，或规范化生成键；三者的比较、索引、pattern matching 和升级代价不同。PostgreSQL 文档明确指出 nondeterministic collation 会带来性能成本、关闭 B-tree deduplication，并限制部分模式匹配。没有写清这些取舍时，不要只加一个 `lower(email)` 索引就宣称问题解决。

### unique 继承相等性

unique constraint 依赖列/索引采用的相等语义。若 collation 认为两个不同字节串相等，唯一约束也会据此冲突。相反，在确定性默认 collation 下，`Alice` 与 `alice` 通常是不同值。业务必须先决定相等，再让 constraint 与 API 使用同一规则。

检查当前数据库可用 collation：

```sql
\dOS+

SELECT
    collname,
    collprovider,
    collisdeterministic,
    collversion
FROM pg_catalog.pg_collation
ORDER BY collname
LIMIT 20;
```

可用名称依赖数据库编码、构建选项和系统/ICU 环境。DDL 不应引用只在开发机存在的 locale 而没有部署前置检查。

## 4.1.3 `date`、`timestamptz`、时区与业务时间 {#item-4-1-3}

“2026-11-01 01:30”可能是一个日期上的当地钟表读数，也可能是某个已经发生的全球瞬间。在纽约夏令时回拨日，这个读数甚至对应两个不同瞬间。类型必须反映要保存的事实：

| 事实 | 合适起点 | 说明 |
|---|---|---|
| 生日、账期、营业日 | `date` | 没有时刻与 zone |
| 已发生的下单/支付瞬间 | `timestamptz` | 全球时间轴上的点 |
| 每天 09:00 的当地日程模板 | `time` + 业务 zone | 还不是具体瞬间 |
| 当地民事日期时间 | `timestamp` + zone name | 解析后才能得到瞬间 |
| 持续时间 | `interval` 或明确单位整数 | 月、日、秒不是同一长度 |

只写 `timestamp` 在 SQL/PostgreSQL 中表示 `timestamp without time zone`。`timestamptz` 是 `timestamp with time zone` 的 PostgreSQL 别名。

### `timestamptz` 保存瞬间，不保存原始时区

timezone-aware 时间在内部按 UTC 瞬间保存，查询输出时再按会话 `TimeZone` 转换。下面两个显示不同，但值相等：

```sql
SET TimeZone = 'UTC';
SELECT '2026-07-29 17:00:00+08'::timestamptz;

SET TimeZone = 'Asia/Shanghai';
SELECT '2026-07-29 09:00:00+00'::timestamptz;
```

因此 `timestamptz` 不会记住输入使用 `Asia/Shanghai`、`CST` 还是 `+08`。如果业务必须保留“用户选择的 IANA zone”，另存并验证 zone name；不要从显示偏移反推。

v1 的验证脚本固定：

```sql
SET TimeZone = 'UTC';
```

这让证据输出与执行机器无关。展示给用户时可以：

```sql
SELECT placed_at AT TIME ZONE 'Asia/Shanghai'
FROM shop.sales_order;
```

`AT TIME ZONE` 的结果类型取决于输入类型；应用边界要明确输出是否仍携带 offset。

### 精度也是合同

PostgreSQL 时间精度 `p` 允许 0–6 位秒后小数。v0 没写，v1 明确使用 `timestamptz(3)`，与常见毫秒 API 对齐。更高精度不是免费“更准确”：上游时钟可能根本没有微秒真实性，跨系统序列化也可能截断。若审计要求微秒，改合同并验证每个生产者，而不是只改数据库列。

字段语义被拆开：

- `created_at`：数据库接受记录的事务时间，默认 `transaction_timestamp()`；
- `placed_at`：订单被业务接受的瞬间；draft 时为 NULL；
- `paid_at` / `cancelled_at`：状态伴随事件；
- `payment.occurred_at`：支付 provider 事件时间。

`transaction_timestamp()`（亦即事务中的 `now()`）在同一事务内保持不变；`statement_timestamp()` 在每条语句开始变化，`clock_timestamp()` 才读取实际墙钟。创建时间默认值使用事务时间可让同一原子命令一致；外部事件时间则必须显式传入，不能用插库时钟覆盖 provider 事实。

### DST 必须用反例验证

[`negative-cases.sql`](/labs/ch04/negative-cases.sql)创建纽约回拨日的两个显式 offset：

```sql
'2026-11-01 01:30:00-04'::timestamptz
'2026-11-01 01:30:00-05'::timestamptz
```

两者相差一小时。若只传无 offset 的 `01:30`，解析依赖会话 zone 规则并产生歧义。事件 API 应接受带 offset 的 ISO 8601，或者同时接收受验证的当地时间和 IANA zone，并定义 DST gap/overlap 策略。

不要写 `CHECK (occurred_at <= now())` 来维护“不能来自未来”。当前时间会变化，restore/replay 时语义也不同；时钟漂移和允许窗口属于命令验证/运营策略。数据库适合维护同一行中稳定的关系，例如：

```text
paid_at >= placed_at
cancelled_at >= placed_at (如果已经 placed)
```

v1 的 `sales_order_state_time_consistent` 同时约束状态与这三个时间。非法的 `paid` 但无 `paid_at` 会被明确拒绝。

### 本节验收

- 金额单位、币种、范围和退款语义均有书面合同；
- 迁移先验证可表示性，不依赖会舍入的 numeric→bigint cast；
- 机器键的 ASCII 语义与人类文本的 Unicode 语义分开；
- 大小写不敏感需求包含正规化、collation、索引和升级策略；
- 能说明 `timestamptz` 保存什么、没有保存什么；
- DST 双重时间和 UTC/Shanghai 投影均由 SQL 反例验证；
- 不使用 volatile 当前时间伪装成永久 `CHECK`。

## 参考资料

- [PostgreSQL 18：数值类型](https://www.postgresql.org/docs/18/datatype-numeric.html)
- [PostgreSQL 18：money 类型](https://www.postgresql.org/docs/18/datatype-money.html)
- [PostgreSQL 18：字符类型](https://www.postgresql.org/docs/18/datatype-character.html)
- [PostgreSQL 18：collation 支持](https://www.postgresql.org/docs/18/collation.html)
- [PostgreSQL 18：日期/时间类型与时区](https://www.postgresql.org/docs/18/datatype-datetime.html)
- [PostgreSQL 18：日期/时间函数与当前时间](https://www.postgresql.org/docs/18/functions-datetime.html)

---

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