卷 III · 类型CH 11深度 11/24

类型当真：钱、时间与那些坑

选类型是建表时最该较真、却最常被随手对付的一步。选错了，代价是隐蔽而昂贵的：用 float 存钱会少一分钱，用裸 timestamp 存时间会在跨时区时算错一小时。PostgreSQL 有一套丰富又严格的类型系统——用好它，就是让数据从存进去的那一刻就名副其实。

numerictimestamptztextboolean

钱：永远用 numeric，永远别用 float

这是最重要、也最反直觉的一条。浮点数（real / double precision）用二进制存小数，而很多十进制小数二进制里存不准。经典演示：

db=# SELECT 0.1::float8 + 0.2::float8;
 ?column?
--------------------
 0.30000000000000004     -- 不是 0.3 ！

db=# SELECT 0.1::numeric + 0.2::numeric;
 ?column?
----------
      0.3               -- numeric 精确

float 存钱，日积月累会累积出「少一分、多一分」的误差，对账时能把人逼疯。numeric(p, s)（也叫 decimal）是精确的十进制，p 是总位数、s 是小数位数。存钱用 numeric(12, 2)，一分不差。

而且 PostgreSQL 会当真地执行这个精度。往 numeric(6, 2) 里塞一个超过 6 位整数的数，它报错拒绝，而不是悄悄截断：

db=# INSERT INTO books(price) VALUES (123456.78);   -- numeric(6,2) 装不下
ERROR:  numeric field overflow
DETAIL:  A field with precision 6, scale 2 must round to
         an absolute value less than 10^4.

时间：用 timestamptz，别用裸 timestamp

时间类型是第二个大坑。PostgreSQL 有两个：

  • timestamp（= timestamp without time zone）：一个「墙上时钟」读数，不带时区信息。存进去是几点，取出来还是几点，没人知道那是哪个时区的几点。
  • timestamptz（= timestamp with time zone）：存的是一个绝对时刻。写入时按你的时区换算成 UTC 存下，读取时再按你的时区换算回来显示。

差别在跨时区时致命。几乎所有场景都该用 timestamptz——它记录「事情发生的那个绝对瞬间」，无论你的服务器、你的用户在哪个时区，都指向同一时刻。裸 timestamp 只适合存「与时区无关的墙上时间」（比如「每天 09:00 开门」这种规则）。

-- 下单时间：绝对时刻，用 timestamptz
ordered_at timestamptz NOT NULL DEFAULT now()

-- now() 返回带时区的当前时刻；current_date 取日期；age(a, b) 算时间差
SELECT now(), current_date, age('2000-01-01');
◆ 建表时选类型的四条默认
  • 钱、精确小数 → numeric（不是 float）。
  • 时刻 → timestamptz（不是 timestamp）。
  • 字符串 → text（不纠结 varchar(n) 的长度）。
  • 是非 → boolean（不是 tinyint / 0/1）。

记住这四条，你的表就避开了 90% 的类型坑。

文本：text 就好，别再纠结长度

PostgreSQL 有 char(n)、varchar(n)、text 三种字符类型，但有个让 MySQL 用户意外的事实：它们性能完全一样，底层是同一套存储。varchar(n) 的 n 只是加了一条「长度不超过 n」的约束——除非你确实有这条业务规则（比如国家码就是 2 位），否则用 text 最省心，永远不会因为「名字比预想的长」而被截断或报错。

至于 char(n)（定长、不足补空格），几乎没有使用理由，忘掉它。

布尔：一个真正的 boolean

PostgreSQL 有独立的 boolean 类型，取值 true / false / NULL（第三档又是那个「不知道」）。它接受很多字面量：'t'、'true'、'yes'、'1'、'on' 都算真。查询里可以直接把它当条件：WHERE is_active，不用写 = 1 或 = true。这是个小而真切的「当真」——是非就用是非类型表示，别拿数字假装。

▶ 动手 · 类型校验台

下面这台真引擎让你看同一个 ::type 转换，PostgreSQL 和历史 MySQL 各自怎么处理。挨个点，注意 PostgreSQL 只在输入确实合法时才放行，否则报错；MySQL 则「能读多少读多少」——'42abc' 取 42、非法日期变 0000-00-00。

类型转换：显式清楚，隐式克制

类型之间的转换用 ::type（PG 特有的简写）或标准的 CAST(x AS type)：

SELECT '42'::integer,  '3.14'::numeric,  now()::date,  42::text;

PostgreSQL 在隐式转换上比 MySQL 克制得多。它不会随便帮你把字符串当数字比较、把数字当字符串拼接——很多情况下你得显式说明。这偶尔让人觉得啰嗦（比如 WHERE id = '42' 这种字符串比整数，PG 可能要你写清楚），但它换来的是不会有意料之外的隐式转换悄悄改变查询语义。又是那个取舍：多打几个字，少踩一个隐蔽的坑。

⇄ 对照 MySQL
  • 没有 UNSIGNED：MySQL 有 INT UNSIGNED（非负整数，范围翻倍）；PostgreSQL 没有 unsigned 整数。要「非负」用 CHECK (x >= 0) 约束表达，或用更大的类型（bigint）。迁移时这是常见改动点。
  • 整数溢出：往整数列塞超范围的值，PostgreSQL 报 integer out of range 拒绝；历史 MySQL 可能悄悄截到最大值。又是「当真 vs 不当真」。
  • DATETIME vs TIMESTAMP：MySQL 这两个类型的语义和自动更新（ON UPDATE CURRENT_TIMESTAMP）、0000-00-00 允许性，历史上一堆坑。PostgreSQL 的 timestamptz 语义清爽得多，也从不接受 0000-00-00 这种非法值。
  • ENUM：MySQL 的 ENUM 是列级的；PostgreSQL 的枚举是独立的类型（CREATE TYPE mood AS ENUM (...)），能被多列共享、能查询它有哪些值——下一章的「超能力类型」会讲。
  • 字符集：MySQL 要在列 / 表上操心 utf8mb4；PostgreSQL 在建库时定好编码，列上不用再管。

这一章的一句话

钱用 numeric、时刻用 timestamptz、字符串用 text、是非用 boolean——选对类型，数据从写入那一刻就名副其实；PostgreSQL 会当真地执行精度和范围，绝不悄悄截断。

类型管住了「每个值长什么样」。下一章的约束，管的是「值与值之间、行与行之间该守什么规矩」——数据库作为最后一道防线，正是靠约束站岗。