4.1 金额、文本与时间

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

4.1.1 整数、numeric 与金额精度

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

PostgreSQL 的主要选择是：

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

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 明确限定单币种人民币：

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

于是：

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先检查：

value * 100 = trunc(value * 100)

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

(value * 100)::bigint

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

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

金额验收

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 返回，错误为：

product price cannot be represented as bounded integer minor units

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

4.1.2 text、排序规则与大小写语义

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

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

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

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

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

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

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

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

至少要回答：

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

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

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：

\dOS+

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

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

4.1.3 date、timestamptz、时区与业务时间

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

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

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

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

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 的验证脚本固定：

SET TimeZone = 'UTC';

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

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创建纽约回拨日的两个显式 offset：

'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 时语义也不同；时钟漂移和允许窗口属于命令验证/运营策略。数据库适合维护同一行中稳定的关系，例如：

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：数值类型
• PostgreSQL 18：money 类型
• PostgreSQL 18：字符类型
• PostgreSQL 18：collation 支持
• PostgreSQL 18：日期/时间类型与时区
• PostgreSQL 18：日期/时间函数与当前时间

返回本章目录 · 下一节：标识、状态与半结构化数据 · 查看全书目录 · 查看索引中心