11. 数值类型

本节目标：学完能根据场景选对整数、小数、浮点类型，避开整数除法陷阱，并知道 SERIAL 已不是推荐写法。

数值类型用来存数字。PostgreSQL 按「是否带小数」「要不要绝对精确」分成几类。选错类型的代价不小：用 integer 存订单号，量一大就溢出；用 real 存金额，算完对不上账。下面按常用程度讲。

整数类型

存没有小数部分的整数，有三个选择：

类型	占用	取值范围
`smallint`	2 字节	-32768 ~ 32767
`integer`（可写 `int`）	4 字节	约 ±21 亿
`bigint`	8 字节	约 ±922 亿亿

integer 是日常首选，存储和性能最平衡。smallint 适合确定很小的数，比如年龄、页数、月份。bigint 留给可能很大的量，比如订单总量、网站的累计访问数。

CREATE TABLE users (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  email VARCHAR(120),
  age SMALLINT,
  created_at TIMESTAMPTZ DEFAULT now()
);

Note

别为了省一两个字节直接用 smallint 存可能变大的数。我见过有人拿 smallint 存「用户积分」，活动一上线积分破三万，直接溢出报错。拿不准就用 integer。

整数除法的坑

两个整数相除，结果还是整数，小数部分会被直接截断，不四舍五入：

SELECT 5 / 2;        -- 结果是 2，不是 2.5
SELECT 7 / 2;        -- 结果是 3

要得到小数，至少让一边变成小数，比如乘个 1.0 或转成 numeric：

SELECT 5.0 / 2;      -- 2.5000000000000000
SELECT 5::numeric / 2;  -- 2.5000

这个坑在算「平均分」「比率」时尤其常见，记得先转类型再除。

精确小数（numeric / decimal）

金额这类「算错一分钱都不行」的数，用 numeric（也叫 decimal，两者完全等价）。语法是 NUMERIC(总位数, 小数位)。

CREATE TABLE orders (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id INT,
  amount NUMERIC(10,2),
  status VARCHAR(20),
  created_at TIMESTAMPTZ DEFAULT now()
);

INSERT INTO orders (user_id, amount) VALUES (1, 99.90);

NUMERIC(10,2) 表示总共最多 10 位数字、其中 2 位是小数，也就是整数最多 8 位，范围到 99999999.99。

超出小数位会四舍五入，超出总位数则报错：

INSERT INTO orders (user_id, amount) VALUES (1, 1234567890.12);
-- ERROR: numeric field overflow
-- DETAIL: A field with precision 10, scale 2 must round to an absolute value less than 10^8.

SELECT NUMERIC '10.005'::numeric(10,2);  -- 10.01（四舍五入）

小数位也可以省略写成 NUMERIC，那样不限制位数和精度，灵活但存储更随意，一般建表时还是写清楚 (p,s) 更稳妥。

Note

numeric 计算是精确的，但比整数和浮点慢，也更占空间。不需要精确的场景（比如身高、温度、评分）别硬用，反而拖累性能。金额、汇率、利率这类必须用它。

浮点类型（real / double precision）

real 约 6 位有效数字，double precision 约 15 位。它们是「近似值」，适合科学计算、物理量测量，不适合金额。

SELECT 0.1::real + 0.2::real AS r;
-- 结果大约是 0.30000001，不是精确的 0.3

两个浮点数直接比相等可能出问题，因为存的是近似值。下面这种情况永远不成立：

SELECT 0.1::double precision + 0.2::double precision = 0.3::double precision;
-- false

Warning

钱千万别用浮点。0.1 + 0.2 在浮点里不等于 0.3，日积月累对账会差出几分钱，财务那边可不会善罢甘休。金额一律 numeric(p,s)。浮点只在不在乎微小误差的测量、图形、统计聚合里用。

PostgreSQL 还有个特殊值 float8/float4 是 double precision/real 的别名，写法随意，但建议写全称更清晰。浮点还支持 NaN（不是数）、Infinity 等特殊值，一般用不到。

SERIAL 仅作历史兼容

老教程里常见 SERIAL 做自增，它本质是一层语法糖：建个序列（sequence），再把默认值设为 nextval。还有 smallserial/bigserial 是对应小/大整数版的 SERIAL。

-- 历史写法，新项目不推荐
CREATE TABLE legacy (id SERIAL PRIMARY KEY);

它的问题在于约束弱、不是 SQL 标准、删表时序列清理不彻底。v18 推荐用 GENERATED ALWAYS AS IDENTITY 来自增主键，它是 SQL 标准写法，约束更强、语义更清晰。具体对比看第 15 章。

一个真实踩坑案例

讲个我见过的真事：有张订单表把 amount 设成 real，运营发现「总价对不上」。原因是 0.1 + 0.2 在浮点里等于 0.30000001，日积月累，成千上万笔订单的舍入误差汇总后变成了几分甚至几毛的差额。改法很简单，把列改成 numeric(10,2)，历史数据用 USING 重算一遍就准了。

另一个常见坑是用 integer 存「比例」或「百分比」。比如想存 0.15 表示 15%，结果 integer 直接把小数截成 0。这类「带小数的业务量」要么用 numeric，要么用「整数分」来存（金额存「分」、比例存「万分之一」），避免浮点和小数截断。

所以记住一条：凡是和「钱、比例、精确计量」相关的，一律 numeric；只有「数个数」才用整数。这条原则能避开绝大多数数值类型的 bug。

类型转换与混用

不同类型之间可以显式转换，用 ::类型 或 CAST(... AS ...)：

SELECT CAST('123' AS INTEGER);   -- 123
SELECT '123'::INTEGER;           -- 123
SELECT 3.14::NUMERIC(4,2);       -- 3.14

数字和字符串也能互转，但字符串里混进非数字会报错：

SELECT '12a'::INTEGER;
-- ERROR: invalid input syntax for type integer: "12a"

所以把文本列改成整数前，先用 SELECT 筛出脏数据，别直接 USING 强转。

各数值类型一览

类型	占用	典型用途
`smallint`	2 字节	年龄、月份、小范围计数
`integer`	4 字节	绝大多数整数、自增主键
`bigint`	8 字节	大计数、订单量、雪花 id
`numeric(p,s)`	变长	金额、汇率等精确小数
`real`	4 字节	科学测量近似值
`double precision`	8 字节	高精度近似计算

选错类型最常见的两个后果：一是 integer 当主键，数据量破 21 亿就溢出插不进；二是金额用 real/double，对账差几分钱。避开这两点，数值类型基本就选对了。

Tip

把字符串列转成数字前，先确认里面没有脏数据，否则 ALTER TABLE ... USING 会中途报错回滚。我的习惯是先用 SELECT col FROM t WHERE col !~ '^[0-9]+$' 筛出非法行，清干净再正式转类型。另外 numeric 相除也可能溢出上限，金额运算记得预留足够精度位数，别把 (p,s) 设得太紧，否则生产环境改表被脏数据卡住很麻烦。

怎么选

普通整数 → integer，小范围用 smallint，超大数据量用 bigint。
金额、需要精确的小数 → numeric(p,s)，记住整数除法要转类型。
科学测量、允许误差 → real / double precision。
自增主键 → GENERATED ALWAYS AS IDENTITY，别用 SERIAL。