11 数值类型

PostgreSQL 提供三大类数值类型：整数、精确小数和浮点数。每一类都有自己的适用场景。整数适合计数器和 ID，速度快、占用空间小。numeric（也写作 decimal）提供精确运算，这对金额至关重要。浮点数（real、double precision）速度快、占用空间小，但可能产生微小的舍入误差，不应用于货币。

本章要点

• 整数类型族：smallint、integer、bigint
• 精确小数：numeric(p, s) 和不带约束的 numeric
• 浮点数：real、double precision
• 精度和标度如何发挥作用
• 何时选择哪一种类型

示例

-- Integer family
SELECT 1::smallint AS s, 1::integer AS i, 1000000::bigint AS b;

-- Range limits (smallint -32768..32767, integer -2.1B..2.1B, bigint very large)
SELECT pg_typeof(1) AS literal_int_type;  -- 'integer' by default

-- Exact decimal: numeric(precision, scale)
-- precision = total digits, scale = digits after the decimal point
SELECT 3.14::numeric(4, 2) AS pi, 12345.67::numeric(7, 2) AS money;

-- Unconstrained numeric is exact within PostgreSQL's implementation limits
SELECT '0.1'::numeric + '0.2'::numeric AS exact;  -- 0.3, exactly

-- Floats are approximate but fast
SELECT 0.1::double precision + 0.2::double precision AS approximate;  -- 0.30000000000000004

-- Useful generators
SELECT generate_series(1, 5) AS n;

integer 是默认的整数类型，1 解析后就是这种类型。当数值可能超过 20 亿时（例如大型表中的行数、自纪元以来的毫秒数等），应改用 bigint。smallint 很少值得使用：它节省的存储空间很少，而超出其范围的值会报错。

numeric(p, s) 强制总位数最多为 p 位，其中 s 位在小数点后。numeric(10, 2) 存储范围从 -99,999,999.99 到 99,999,999.99。不带约束的 numeric（没有 p、s）在 PostgreSQL 的实现限制内存储精确的小数值，速度比定长整数慢，但结果精确。它适合金额、实验室测量值，以及任何不能接受舍入误差的场景。

real（4 字节，约 6 位有效数字）和 double precision（8 字节，约 15 位有效数字）都是 IEEE 754 浮点数。它们速度快、占用空间小，但 0.1 + 0.2 不会精确等于 0.3。如果可以容忍不精确，它们适合物理测量、机器学习特征和统计聚合。

运行示例

运行方式：

$ psql -f source/numeric-types.sql postgres
 s | i |    b
---+---+---------
 1 | 1 | 1000000
(1 row)
 ...
 exact
-------
 0.3
(1 row)

   approximate
------------------
 0.30000000000000004
(1 row)

常见陷阱

• 用 double precision 存储金额——迟早会在分币舍入上出问题。请使用 numeric(p, 2)（或不带范围限制的 numeric），让数据库执行精确运算。
• 在大型表中为 ID 列选择 integer——可能触及 21 亿的上限。对于预计会增长的主键，默认使用 bigint。
• 对含有非数字字符的字符串做类型转换：'$5'::numeric 会报错。先去除这些字符，再做类型转换。
• 整数相除可能让你感到意外：7 / 2 的结果是 3，而不是 3.5。可以做类型转换：7::numeric / 2。

下载

SQL 文件：numeric-types.sql · 全部示例文件 · 准备工作