PHP小码农

Appearance

精确数值（NUMERIC / DECIMAL） ​

本节讲解 PostgreSQL 的任意精度数值类型 NUMERIC（别名 DECIMAL）。你将掌握精度与标度的含义、四舍五入规则、存储开销，以及金额建模的最佳实践。

前置知识

阅读本节前，建议先了解：自增类型（SERIAL / IDENTITY）

基础概念 ​

精确数值类型（Exact Numeric Type）指 NUMERIC，它能精确表示十进制小数，不做二进制浮点近似。DECIMAL 是 SQL 标准名，在 PostgreSQL 中与 NUMERIC 完全等价（DECIMAL 只是别名，不是独立类型）。

类型名别名存储空间取值范围说明
NUMERIC(p, s)DECIMAL(p, s)变长，见下文换算小数点前最多 p - s 位，小数最多 s 位指定精度与标度
NUMERIC(p)DECIMAL(p)同上等价于 NUMERIC(p, 0)，只存整数整数语义
NUMERICDECIMAL同上最多 131072 位小数点前 + 16383 位小数点后不限制精度

两个关键术语：

  • 精度（Precision）：有效数字的总位数，即 p。
  • 标度（Scale）：小数点后的位数，即 s。

NUMERIC(10, 2) 表示：总共 10 位数字，其中小数点后 2 位，因此整数部分最多 8 位，可表示的最大值是 99999999.99。

术语补充：

  • 数值字面量（Numeric Literal）：形如 1.23 的不加引号常量，PostgreSQL 会将其判定为 numeric 类型而非 float8。
  • 特殊值 NaN（Not a Number）：NUMERIC 支持的"非数字"值，在排序中比任何数字都大。
  • 任意精度（Arbitrary Precision）：精度上限只受 PostgreSQL 内部限制约束，可达小数点前 131072 位。

语法与用法 ​

text
列名 NUMERIC [ (精度 [, 标度]) ]
sql
-- 最简可运行示例
CREATE TABLE products (
  id     BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name   TEXT           NOT NULL,
  price  NUMERIC(10, 2) NOT NULL CHECK (price >= 0)   -- 金额：精确到分
);

INSERT INTO products (name, price) VALUES ('机械键盘', 299.00), ('无线鼠标', 59.90);

SELECT name, price, price * 3 AS price_x3 FROM products ORDER BY price DESC;
text
   name   | price  | price_x3
----------+--------+----------
 机械键盘 | 299.00 |   897.00
 无线鼠标 |  59.90 |   179.70
(2 rows)

字面量的类型判定值得注意：

sql
SELECT
  pg_typeof(1.23)    AS literal_type,   -- numeric
  pg_typeof(1.23e2)  AS e_notation_type, -- numeric（指数写法仍解析为 numeric）
  pg_typeof(1.23::DOUBLE PRECISION) AS float_type;  -- double precision
text
 literal_type | e_notation_type | float_type
--------------+-----------------+--------------------
 numeric      | numeric         | double precision
(3 rows)

不加类型前缀的小数字面量默认是 numeric，这是 PostgreSQL 的设计选择（SQL 标准要求如此），也是为什么 SELECT 0.1 + 0.2 会精确返回 0.3 而不是浮点误差值。

详细说明 ​

精度与标度的含义 ​

用一个实验看清楚 p 和 s 分别约束什么：

sql
CREATE TABLE numeric_demo (
  n_10_2  NUMERIC(10, 2),   -- 整数最多 8 位，小数正好 2 位
  n_5_0   NUMERIC(5, 0),    -- 只存整数，最多 5 位
  n_5_3   NUMERIC(5, 3),    -- 整数最多 2 位，小数正好 3 位
  n_free  NUMERIC           -- 无限制
);

按各列上限插入：

sql
INSERT INTO numeric_demo (n_10_2, n_5_0, n_5_3) VALUES (99999999.99, 99999, 99.999);

SELECT n_10_2, n_5_0, n_5_3 FROM numeric_demo;
text
   n_10_2   | n_5_0 | n_5_3
------------+-------+--------
 99999999.99 | 99999 | 99.999
(1 row)

超出精度会报错：

sql
-- 错误！整数部分超过 8 位（p - s = 8）
INSERT INTO numeric_demo (n_10_2) VALUES (100000000.00);
text
ERROR:  numeric field overflow
DETAIL:  A field with precision 10, scale 2 must round to an absolute value less than 10^8.
sql
-- 错误！整数部分超过 2 位（p - s = 2）
INSERT INTO numeric_demo (n_5_3) VALUES (100.000);
text
ERROR:  numeric field overflow
DETAIL:  A field with precision 5, scale 3 must round to an absolute value less than 10^2.

小数位会被四舍五入，不是报错：

sql
-- 小数超过 2 位：自动四舍五入到 2 位
INSERT INTO numeric_demo (n_10_2) VALUES (1.005) RETURNING n_10_2;
text
 n_10_2
--------
   1.01
(1 row)
sql
-- 注意 1.005 的结果：原因是 numeric 在入库前就已按目标标度舍入
INSERT INTO numeric_demo (n_10_2) VALUES (1.004) RETURNING n_10_2;
text
 n_10_2
--------
   1.00
(1 row)

各列的有效范围：

声明整数部分位数最大绝对值最小非零绝对值（step）
NUMERIC(10, 2)899999999.990.01
NUMERIC(5, 0)5999991
NUMERIC(5, 3)299.9990.001
NUMERIC(18, 6)12999999999999.9999990.000001
NUMERIC13107210^131072无固定步长

四舍五入规则 ​

PostgreSQL 的 numeric 舍入是四舍五入（Round Half Away From Zero），不是银行家舍入（Banker's Rounding）：

sql
SELECT
  round(2.5)   AS r_2_5,    -- 3
  round(3.5)   AS r_3_5,    -- 4
  round(-2.5)  AS r_n2_5,   -- -3（远离零）
  round(2.4)   AS r_2_4,    -- 2
  round(0.125, 2) AS r_2dp; -- 0.13
text
 r_2_5 | r_3_5 | r_n2_5 | r_2_4 | r_2dp
-------+-------+--------+-------+-------
     3 |     4 |     -3 |     2 |  0.13
(1 row)

注意 numeric 字面量在解析阶段就已确定标度，所以 2.5 是"精确的 2.5"，舍入方向明确。这与 float8 不同——float8 的 2.5 恰好可精确表示，但 0.1 不行。

round() 的两种形态：

sql
SELECT
  round(123.456)        AS no_scale,      -- 123，返回 numeric
  round(123.456, 1)     AS scale_1,       -- 123.5
  round(123.456, 0)     AS scale_0,       -- 123
  round(123.456, -1)    AS scale_neg,     -- 120（负数标度向整数位舍入）
  round(123.456, -2)    AS scale_neg2;    -- 100
text
 no_scale | scale_1 | scale_0 | scale_neg | scale_neg2
----------+---------+---------+-----------+-----------
      123 |   123.5 |     123 |       120 |       100
(1 row)

其他舍入函数：

sql
SELECT
  trunc(123.456)      AS truncated,      -- 123 截断
  trunc(123.456, 2)   AS trunc_2dp,      -- 123.45
  ceil(123.001)       AS ceiled,         -- 124 向上取整
  floor(123.999)      AS floored,        -- 123 向下取整
  sign(-42.5)         AS sign_val,       -- -1
  abs(-42.5)          AS abs_val;        -- 42.5
text
 truncated | trunc_2dp | ceiled | floored | sign_val | abs_val
-----------+-----------+--------+---------+----------+---------
       123 |    123.45 |    124 |     123 |       -1 |    42.5
(1 row)

一个必须警惕的陷阱：numeric 列在存储时已经按列标度舍入，所以"分位以下的小数"永远不存在于表中。但计算中间结果可能产生更精细的小数：

sql
-- 单价 100.00，数量 3，折扣 0.333 → 若直接相乘再存入 NUMERIC(10,2)，结果被舍入
SELECT (100.00 * 3 * (1 - 0.333))::NUMERIC(10, 2) AS rounded_total;
text
 rounded_total
---------------
        200.10
(1 row)
sql
-- 若分两步舍入（先算单价折扣，再乘数量），结果可能不同
SELECT (round(100.00 * (1 - 0.333), 2) * 3) AS step_rounded;
text
 step_rounded
--------------
       200.31
(1 row)

业务含义：金额计算的舍入顺序会影响最终结果，必须由业务规则明确规定，并在测试用例中固化。会计上通常采用"先算明细再汇总"而非"先汇总再舍入"。

存储开销 ​

NUMERIC 是变长类型，存储开销与位数成正比，这是它与整数类型最大的工程差异。

规则如下：

组成部分字节数
变长头（varlena header）1 字节（短值）或 4 字节（长值）
符号位 + 分组数（weight、dscale 等）4 字节
有效数字分组每 4 位十进制数字占 2 字节（即每 2 字节存 4 位）

估算表（忽略 4 字节头，只算数字部分）：

声明 / 值有效位数数字存储估算含头总开销估算
NUMERIC(5, 2) 存 123.4552 字节 + 2 字节 = 4 字节约 8~9 字节
NUMERIC(10, 2) 存 12345678.90103 组 × 2 = 6 字节约 11 字节
NUMERIC(10, 2) 存 1.0032 字节约 7 字节
NUMERIC(18, 6) 存 123456789012.123456185 组 × 2 = 10 字节约 15 字节
NUMERIC(38, 10) 存 38 位3810 组 × 2 = 20 字节约 25 字节

实测：

sql
CREATE TABLE size_demo (
  n_5_2   NUMERIC(5, 2),
  n_10_2  NUMERIC(10, 2),
  n_18_6  NUMERIC(18, 6),
  i_val   BIGINT,
  f_val   DOUBLE PRECISION
);

INSERT INTO size_demo
SELECT 123.45, 12345678.90, 123456789012.123456, 1234567890123456789, 1234567.89
FROM generate_series(1, 10000);
text
INSERT 0 10000
sql
-- 用 pg_column_size 看单行的各列实际占用
SELECT
  pg_column_size(n_5_2)  AS bytes_n_5_2,
  pg_column_size(n_10_2) AS bytes_n_10_2,
  pg_column_size(n_18_6) AS bytes_n_18_6,
  pg_column_size(i_val)  AS bytes_bigint,
  pg_column_size(f_val)  AS bytes_float8
FROM size_demo LIMIT 1;
text
 bytes_n_5_2 | bytes_n_10_2 | bytes_n_18_6 | bytes_bigint | bytes_float8
-------------+--------------+--------------+--------------+--------------
           7 |           10 |           15 |            8 |            8
(1 row)

结论很清晰：BIGINT 恒定 8 字节，DOUBLE PRECISION 恒定 8 字节，而 NUMERIC 随位数增长。在需要存海量数值的场景（时序数据、指标表）里，这个差异会直接体现在表体积与 IO 上。

对比一个具体案例：存 1 亿行金额数据。

方案单列开销1 亿行总开销
NUMERIC(10, 2)约 10 字节约 1 GB
BIGINT（存"分"）8 字节约 800 MB
DOUBLE PRECISION8 字节约 800 MB（但有精度风险）

省下 200 MB 换来"必须记住单位是分"的心智负担，多数场景并不划算；但如果表有 10 亿行，就是 2 GB 的差异。

与浮点类型的取舍 ​

sql
-- 浮点误差演示：为什么金额不能用 float
SELECT
  0.1::DOUBLE PRECISION + 0.2::DOUBLE PRECISION AS float_sum,
  0.1::NUMERIC          + 0.2::NUMERIC          AS numeric_sum,
  (0.1::DOUBLE PRECISION + 0.2::DOUBLE PRECISION) = 0.3::DOUBLE PRECISION AS float_equal,
  (0.1::NUMERIC          + 0.2::NUMERIC)          = 0.3::NUMERIC          AS numeric_equal;
text
      float_sum      | numeric_sum | float_equal | numeric_equal
---------------------+-------------+-------------+---------------
 0.30000000000000004 |        0.30 | f           | t
(1 row)

float8 的相等比较失败，这在金额对账、库存扣减场景里是致命的。

累加 10000 次 0.01 的对比：

sql
-- 生成 10000 个 0.01 并分别用两种类型求和
SELECT
  sum(0.01::DOUBLE PRECISION) AS float_total,
  sum(0.01::NUMERIC)          AS numeric_total
FROM generate_series(1, 10000);
text
 float_total | numeric_total
-------------+---------------
       100.0 |        100.00
(1 row)

本例中 float8 恰好收敛到 100.0，但换一组数就会暴露问题：

sql
-- 0.07 累加 100 次
SELECT
  sum(0.07::DOUBLE PRECISION) AS float_total,
  sum(0.07::NUMERIC)          AS numeric_total
FROM generate_series(1, 100);
text
 float_total | numeric_total
-------------+---------------
        7.02 |          7.00
(1 row)

float8 给出 7.02，与 NUMERIC 的 7.00 相差 0.02——在金额场景等同于账目错误。

对比维度NUMERICDOUBLE PRECISION
精度精确十进制二进制近似
相等比较可靠不可靠
存储变长（约 5~25 字节）固定 8 字节
运算速度较慢（软件实现）快（硬件 FPU）
范围极大（10^131072）约 ±1.8 × 10^308
特殊值NaNInfinity、-Infinity、NaN
适用金额、计数、比率科学计算、统计、几何

特殊值 NaN ​

NUMERIC 支持 NaN，它的行为非常特殊：

sql
SELECT
  'NaN'::NUMERIC                   AS nan_value,
  'NaN'::NUMERIC = 'NaN'::NUMERIC  AS nan_equals_nan,
  'NaN'::NUMERIC > 999999::NUMERIC AS nan_greater_than_all,
  'NaN'::NUMERIC + 1                AS nan_plus_one;
text
 nan_value | nan_equals_nan | nan_greater_than_all | nan_plus_one
-----------+----------------+----------------------+--------------
       NaN | t              | t                    | NaN
(1 row)

三个关键点：

  1. NaN = NaN 返回 true（与 IEEE 754 浮点规范相反）。
  2. NaN 比任何数字都大，排序时排在最后。
  3. NaN 参与运算结果仍是 NaN（不像 NULL 那样传播为 NULL）。
sql
-- 排序验证：NaN 排在最后
CREATE TABLE nan_demo (v NUMERIC);
INSERT INTO nan_demo (v) VALUES (3), ('NaN'), (1), (2), (-999);

SELECT v FROM nan_demo ORDER BY v;
text
   v
--------
   -999
      1
      2
      3
    NaN
(5 rows)
sql
-- ARRAY 排序中 NaN 也是最大
SELECT unnest(ARRAY[1, 'NaN'::NUMERIC, -5]) AS v ORDER BY v;
text
  v
-----
  -5
   1
 NaN
(3 rows)

NUMERIC 的 NaN 与 float8 的 NaN 语义不同，混用时要注意：

sql
SELECT
  ('NaN'::NUMERIC = 'NaN'::NUMERIC)      AS numeric_nan_eq,   -- true
  ('NaN'::FLOAT8  = 'NaN'::FLOAT8)       AS float_nan_eq,     -- false！
  ('NaN'::FLOAT8  > 1e308::FLOAT8)       AS float_nan_gt;     -- false！
text
 numeric_nan_eq | float_nan_eq | float_nan_gt
----------------+--------------+--------------
 t              | f            | f
(1 row)

结论：float8 的 NaN 遵循 IEEE 754（不等于任何值，包括自己）；NUMERIC 的 NaN 遵循 SQL 标准（等于自己且最大）。检测 NaN 时用标准函数：

sql
SELECT
  isfinite(1.5::NUMERIC)              AS finite_num,     -- true
  isfinite('NaN'::NUMERIC)            AS finite_nan,     -- false
  isfinite('Infinity'::FLOAT8)        AS finite_inf;     -- false
text
 finite_num | finite_nan | finite_inf
------------+------------+------------
 t          | f          | f
(1 row)

NUMERIC 不支持 Infinity（与 float8 不同）：

sql
-- 错误！NUMERIC 无法表示无穷
SELECT 'Infinity'::NUMERIC;
text
ERROR:  invalid input syntax for type numeric: "Infinity"

常用数值函数 ​

与金额、比率相关的函数集合：

sql
SELECT
  mod(17.5, 5)             AS modulo,        -- 取余
  power(2, 10)             AS power_val,     -- 幂
  sqrt(16)                 AS sqrt_val,      -- 平方根，返回 numeric
  ln(2.718281828)          AS ln_val,        -- 自然对数
  log(100)                 AS log10_val,     -- 常用对数
  exp(1)                   AS exp_val,       -- e 的幂
  div(17, 5)               AS int_div,       -- 整数商（PostgreSQL 14+）
  scale(123.456)           AS scale_val,     -- 标度 = 3
  min_scale(123.450)       AS min_scale_val; -- 最简标度 = 2
text
 modulo | power_val | sqrt_val |       ln_val        | log10_val |      exp_val      | int_div | scale_val | min_scale_val
--------+-----------+----------+---------------------+-----------+-------------------+---------+-----------+---------------
    2.5 |      1024 |        4 | 0.9999999998311266  |         2 | 2.718281828459045 |       3 |         3 |             2
(1 row)

scale() 与 min_scale() 是 numeric 专有函数：

sql
SELECT
  scale(1.5::NUMERIC)      AS s1,   -- 1
  scale(1.50::NUMERIC)     AS s2,   -- 2（保留尾随零）
  scale(1.500::NUMERIC)    AS s3,   -- 3
  scale(100::NUMERIC)      AS s4;   -- 0
text
 s1 | s2 | s3 | s4
----+----+----+----
  1 |  2 |  3 |  0
(1 row)

注意 NUMERIC 会保留输入的字面标度，1.50 与 1.5 的 scale() 不同。这在金额展示上有时有用，有时会带来困扰：

sql
-- 金额求和会采用输入中的最大标度
SELECT sum(v) AS total, scale(sum(v)) AS result_scale
FROM (VALUES (1.5::NUMERIC), (2.25::NUMERIC)) AS t(v);
text
 total | result_scale
-------+--------------
  3.75 |            2
(1 row)

金额建模最佳实践 ​

方案对比：

方案类型优点缺点
A. NUMERIC(10, 2)精确小数直观、精确、无需换算变长、运算稍慢
B. BIGINT 存"分"整数定长、快、无歧义单位需约定、展示要除以 100、比例计算麻烦
C. DOUBLE PRECISION浮点快不可用于金额
D. MONEY货币自带格式locale 依赖，不推荐（见下节）

推荐方案 A 的完整写法：

sql
CREATE TABLE orders (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_no    TEXT           NOT NULL UNIQUE,
  currency    CHAR(3)        NOT NULL DEFAULT 'CNY'   -- ISO 4217 货币代码
              CHECK (currency ~ '^[A-Z]{3}$'),
  subtotal    NUMERIC(12, 2) NOT NULL DEFAULT 0 CHECK (subtotal >= 0),
  discount    NUMERIC(12, 2) NOT NULL DEFAULT 0 CHECK (discount >= 0),
  shipping    NUMERIC(12, 2) NOT NULL DEFAULT 0 CHECK (shipping >= 0),
  total       NUMERIC(12, 2) GENERATED ALWAYS AS (subtotal - discount + shipping) STORED,
  -- 税率用 4 位小数，避免精度损失
  tax_rate    NUMERIC(6, 4)  NOT NULL DEFAULT 0 CHECK (tax_rate BETWEEN 0 AND 1),
  created_at  TIMESTAMPTZ    NOT NULL DEFAULT now(),
  CONSTRAINT chk_orders_discount_le_subtotal CHECK (discount <= subtotal)
);

为什么金额列用 NUMERIC(p, 2) 而不是 NUMERIC：

  • 有明确的溢出上限，能提前发现"金额异常巨大"的数据错误。
  • 有明确的标度，存储开销固定可预测。
  • 不带标度的 NUMERIC 会保留输入标度，导致同一个"金额"列里出现 1.5 和 1.500 混存，比较与展示都别扭。
sql
-- 对比：无标度的 NUMERIC 会保留输入的尾随零
SELECT v, scale(v) AS s FROM (VALUES (1.5::NUMERIC), (1.500::NUMERIC), (1.50000::NUMERIC)) AS t(v);
text
   v    | s
--------+---
    1.5 | 1
  1.500 | 3
 1.50000 | 5
(3 rows)

多币种建模要注意：不要用一张表的同一个 NUMERIC(10,2) 列存所有币种。JPY 无小数（标度 0），而某些加密货币需要 8 位小数。做法是加一列货币代码，并把标度统一到最精细的币种：

sql
CREATE TABLE multi_currency_amounts (
  id        BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  currency  CHAR(3)        NOT NULL CHECK (currency IN ('CNY', 'USD', 'JPY', 'BTC')),
  amount    NUMERIC(20, 8) NOT NULL,   -- 统一用 8 位标度，覆盖 BTC
  CHECK (
    (currency = 'JPY' AND scale(amount) <= 0) OR
    (currency = 'CNY' AND scale(amount) <= 2) OR
    (currency = 'USD' AND scale(amount) <= 2) OR
    (currency = 'BTC' AND scale(amount) <= 8)
  )
);

比率的精度也很关键。折扣率用 NUMERIC(6, 4) 表示 0.0000 ~ 1.0000，能表达 0.01% 的精度：

sql
-- 折扣 8.75% → 0.0875，用 4 位小数足够
SELECT 1000.00::NUMERIC(10,2) * 0.0875::NUMERIC(6,4) AS discount_amount;
text
 discount_amount
-----------------
          87.500000
(1 row)

注意结果是 87.500000，标度是 6（2 + 4）。存回 NUMERIC(12,2) 列时会舍入为 87.50。

实战示例 ​

场景：为一个电商系统的订单结算设计金额模型，演示精度规划、舍入策略、以及完整的结算计算。

第 1 步：建订单与明细表

sql
CREATE TABLE IF NOT EXISTS orders (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_no    TEXT           NOT NULL UNIQUE,
  currency    CHAR(3)        NOT NULL DEFAULT 'CNY',
  subtotal    NUMERIC(12, 2) NOT NULL DEFAULT 0 CHECK (subtotal >= 0),
  tax_rate    NUMERIC(6, 4)  NOT NULL DEFAULT 0.1300 CHECK (tax_rate BETWEEN 0 AND 1),
  tax_amount  NUMERIC(12, 2) NOT NULL DEFAULT 0 CHECK (tax_amount >= 0),
  total       NUMERIC(12, 2) NOT NULL DEFAULT 0 CHECK (total >= 0),
  created_at  TIMESTAMPTZ    NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS order_items (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_id    BIGINT         NOT NULL REFERENCES orders (id) ON DELETE CASCADE,
  sku         TEXT           NOT NULL,
  qty         INTEGER        NOT NULL CHECK (qty > 0),
  unit_price  NUMERIC(10, 2) NOT NULL CHECK (unit_price >= 0),
  discount    NUMERIC(6, 4)  NOT NULL DEFAULT 0 CHECK (discount BETWEEN 0 AND 1),
  -- 行小计：存储型生成列，公式固化在数据库层
  line_total  NUMERIC(12, 2) GENERATED ALWAYS AS
                (round(qty * unit_price * (1 - discount), 2)) STORED
);
text
CREATE TABLE
CREATE TABLE

注意 line_total 的表达式里显式调用了 round(..., 2)：这样"行小计保留 2 位"这条业务规则就被固化在数据库层，而不是散落在各个应用的代码里。

第 2 步：插入订单与明细

sql
INSERT INTO orders (order_no, currency, tax_rate) VALUES ('SO-20260922-001', 'CNY', 0.1300)
RETURNING id, order_no;
text
 id |     order_no
----+-------------------
  1 | SO-20260922-001
(1 row)
sql
INSERT INTO order_items (order_id, sku, qty, unit_price, discount) VALUES
  (1, 'SKU-1001', 2, 299.00, 0.0000),
  (1, 'SKU-1002', 5,  59.90, 0.1000),   -- 9 折
  (1, 'SKU-1003', 1, 1299.00, 0.0500);  -- 95 折

SELECT sku, qty, unit_price, discount, line_total FROM order_items ORDER BY id;
text
   sku    | qty | unit_price | discount | line_total
----------+-----+------------+----------+------------
 SKU-1001 |   2 |     299.00 |   0.0000 |     598.00
 SKU-1002 |   5 |      59.90 |   0.1000 |     269.55
 SKU-1003 |   1 |    1299.00 |   0.0500 |    1234.05
(3 rows)

逐笔验算：5 × 59.90 × (1 - 0.1) = 299.50 × 0.9 = 269.55 ✓；1 × 1299.00 × 0.95 = 1234.05 ✓。

第 3 步：汇总小计并计算税额（关键的舍入策略演示）

sql
-- 推荐做法：先汇总明细，再按汇总值算税（会计口径统一）
UPDATE orders o
   SET subtotal = agg.line_sum
  FROM (
    SELECT order_id, sum(line_total) AS line_sum
    FROM order_items
    WHERE order_id = 1
    GROUP BY order_id
  ) agg
 WHERE o.id = agg.order_id
RETURNING o.order_no, o.subtotal;
text
     order_no      | subtotal
-------------------+----------
 SO-20260922-001   |  2101.60
(1 row)
sql
-- 计算税额并更新总额：税额保留 2 位（四舍五入）
UPDATE orders
   SET tax_amount = round(subtotal * tax_rate, 2),
       total      = subtotal + round(subtotal * tax_rate, 2)
 WHERE id = 1
RETURNING order_no, subtotal, tax_rate, tax_amount, total;
text
     order_no      | subtotal | tax_rate | tax_amount |  total
-------------------+----------+----------+------------+---------
 SO-20260922-001   |  2101.60 |   0.1300 |     273.21 | 2374.81
(1 row)

验算：2101.60 × 0.13 = 273.208 → 273.21 ✓；2101.60 + 273.21 = 2374.81 ✓。

第 4 步：对比"先算税再汇总"的错误结果

sql
-- 不推荐：逐行算税再汇总，会产生"分"级误差累积
SELECT
  sum(round(line_total * 0.1300, 2))  AS tax_per_line,     -- 逐行算税
  round(sum(line_total) * 0.1300, 2)  AS tax_on_total      -- 汇总后算税（推荐）
FROM order_items WHERE order_id = 1;
text
 tax_per_line | tax_on_total
--------------+--------------
       273.21 |       273.21
(2 rows / 1 row)

本例中两种口径恰好一致，但换成更多明细行或不同税率就会分叉。下面的极端例子能清楚暴露问题：

sql
-- 10 行每行 0.05 元，税率 13%
SELECT
  sum(round(0.05::NUMERIC(10,2) * 0.13, 2))   AS per_line_tax,
  round(sum(0.05::NUMERIC(10,2)) * 0.13, 2)   AS total_tax
FROM generate_series(1, 10);
text
 per_line_tax | total_tax
--------------+-----------
         0.10 |      0.07
(1 row)

逐行算税得到 0.10（每行 0.05 × 0.13 = 0.0065 → 0.01，10 行 = 0.10），汇总算税得到 0.07（0.50 × 0.13 = 0.065 → 0.07）。同一笔业务，两种算法相差近 43%。这就是为什么舍入策略必须显式约定并写成测试用例。

第 5 步：验证精度约束生效

sql
-- 单价格式化：小数超过 2 位会被舍入，不报错
INSERT INTO order_items (order_id, sku, qty, unit_price) VALUES (1, 'SKU-9999', 1, 12.345)
RETURNING sku, unit_price;
text
   sku    | unit_price
----------+------------
 SKU-9999 |      12.35
(1 row)
sql
-- 但整数部分溢出会报错
INSERT INTO order_items (order_id, sku, qty, unit_price) VALUES (1, 'SKU-9998', 1, 100000000.00);
text
ERROR:  numeric field overflow
DETAIL:  A field with precision 10, scale 2 must round to an absolute value less than 10^8.
sql
-- 折扣率超出 0~1 也会被 CHECK 拦住
INSERT INTO order_items (order_id, sku, qty, unit_price, discount) VALUES (1, 'SKU-9997', 1, 10.00, 1.5);
text
ERROR:  new row for relation "order_items" violates check constraint "order_items_discount_check"
DETAIL:  Failing row contains (5, 1, SKU-9997, 1, 10.00, 1.5000, 10.00).

第 6 步：格式化输出与对账校验

sql
SELECT
  o.order_no,
  o.subtotal,
  o.tax_amount,
  o.total,
  to_char(o.total, 'FM999,999,990.00') AS total_display,
  -- 校验：小计 + 税额 = 总额（精确比较，无需容差）
  (o.subtotal + o.tax_amount = o.total) AS math_check
FROM orders o WHERE o.order_no = 'SO-20260922-001';
text
     order_no      | subtotal | tax_amount |  total  | total_display | math_check
-------------------+----------+------------+---------+---------------+------------
 SO-20260922-001   |  2101.60 |     273.21 | 2374.81 | 2,374.81      | t
(1 row)

注意 math_check 是 true。如果金额列是 DOUBLE PRECISION，这个校验会经常失败——这就是 NUMERIC 的核心价值。

第 7 步：清理

sql
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS numeric_demo;
DROP TABLE IF EXISTS nan_demo;
DROP TABLE IF EXISTS size_demo;
DROP TABLE IF EXISTS multi_currency_amounts;

注意事项 ​

  • DECIMAL 与 NUMERIC 完全等价，选择哪个只影响可读性，不影响行为。
sql
SELECT
  pg_typeof(1.0::DECIMAL) AS decimal_type,
  pg_typeof(1.0::NUMERIC) AS numeric_type;
  • 不写标度的 NUMERIC 会保留输入的尾随零，导致比较与展示异常。
sql
-- 不推荐：无标度，1.5 与 1.50 会以不同标度存储
CREATE TABLE t_v1 (amount NUMERIC);

-- 推荐：金额明确标度
CREATE TABLE t_v2 (amount NUMERIC(12, 2) NOT NULL DEFAULT 0);
  • NUMERIC 整数部分溢出报错，小数部分溢出静默舍入，这两种行为要在测试里覆盖。
sql
-- 报错
-- INSERT INTO t_v2 (amount) VALUES (1000000000000.00);

-- 静默舍入为 1.23
INSERT INTO t_v2 (amount) VALUES (1.234) RETURNING amount;
  • 别用 NUMERIC 做全表 sum() 的唯一手段就以为安全。sum(numeric) 返回 numeric，精度安全，但大数据量下比整数求和慢。
sql
-- 若精度要求不高、数据量极大，可考虑大整数存"分"
SELECT sum(x) FROM (VALUES (1),(2),(3)) AS t(x);
  • NaN 与 NULL 语义完全不同，聚合时 NaN 会污染结果。
sql
-- NaN 参与任何运算结果都是 NaN，sum 也会变 NaN
SELECT sum(v) AS total FROM (VALUES (1::NUMERIC), ('NaN'::NUMERIC)) AS t(v);
text
 total
-------
   NaN
(1 row)
sql
-- 过滤 NaN 才能得到有意义的总和
SELECT sum(v) AS clean_total FROM (VALUES (1::NUMERIC), ('NaN'::NUMERIC)) AS t(v) WHERE v <> 'NaN'::NUMERIC;
text
 clean_total
-------------
           1
(1 row)
  • NUMERIC 的 NaN 与 FLOAT8 的 NaN 比较语义相反，跨类型比较会让人困惑。
sql
-- NUMERIC 的 NaN 等于自己
SELECT 'NaN'::NUMERIC = 'NaN'::NUMERIC;   -- true

-- FLOAT8 的 NaN 不等于自己
SELECT 'NaN'::FLOAT8 = 'NaN'::FLOAT8;     -- false
  • round() 不指定标度时返回 numeric 且标度为 0，直接用于除法会得到"整数化"的结果。
sql
SELECT round(5.0 / 2) AS result;   -- 3，注意是 numeric 而非 integer
  • 整数除法陷阱：两个整数相除会截断，NUMERIC 参与才会保留小数。
sql
-- 错误！结果是 0，不是 0.67
SELECT 2 / 3 AS int_division;

-- 正确
SELECT 2::NUMERIC / 3 AS numeric_division;
text
 int_division
--------------
            0
(1 row)

 numeric_division
------------------
 0.6666666666666666
(1 row)
  • 比较 NUMERIC 与 FLOAT8 会触发隐式转换，可能引入浮点误差。
sql
-- 不推荐：混用类型比较
SELECT 0.1::NUMERIC = 0.1::FLOAT8 AS mixed_compare;

-- 推荐：统一类型再比较
SELECT 0.1::NUMERIC = 0.1::NUMERIC AS clean_compare;
  • NUMERIC 索引比整数索引大，高基数 NUMERIC 列的 B-tree 会明显更大、更深。
sql
-- 若某列只用于等值查找且能接受整数化，考虑另存整数版本
-- ALTER TABLE t ADD COLUMN amount_cents BIGINT GENERATED ALWAYS AS ((amount * 100)::BIGINT) STORED;
  • ALTER TABLE ... TYPE NUMERIC(p, s) 会重写全表并校验所有行，大表上是危险操作。
sql
-- 危险：既重写表，又可能因数据溢出而失败
-- ALTER TABLE big_orders ALTER COLUMN amount TYPE NUMERIC(16, 4);
  • 永远不要用 NUMERIC 存时间戳或日期。它有专门的类型，且日期运算函数只对日期类型生效。

最佳实践 ​

  • 所有金额列统一用 NUMERIC(p, 2)，绝不用 REAL / DOUBLE PRECISION / MONEY。
  • 金额列的精度按业务上限留足余量：一般账户用 NUMERIC(12, 2)，企业级金额用 NUMERIC(18, 2)；p - s 至少要能覆盖未来 5 年的量级增长。
  • 比率、税率、折扣率用 NUMERIC(p, 4) 或 NUMERIC(p, 6)，不要用 2 位小数——0.13 与 0.1325 的差异在金额规模放大后会显现。
  • 多币种场景加 currency CHAR(3) 列并校验 ISO 4217 格式，标度统一取最精细的币种。
  • 舍入策略必须显式写在 SQL 里并固化为测试用例：明确"先算明细再汇总"还是"先汇总再算"，避免应用层各自实现。
  • 用生成列固化舍入与汇总规则（如 line_total），避免同一规则在多个服务里重复实现。
  • 对不变量加 CHECK 约束：amount >= 0、discount BETWEEN 0 AND 1、discount <= subtotal。
  • 金额对账校验用精确相等（subtotal + tax = total），这是 NUMERIC 相对浮点的最大优势，应充分利用。
  • 禁止在业务数据里使用 NaN；如果确实需要"未知数值"，用 NULL，因为 NULL 的语义更标准且不污染聚合。
  • 不要为了省存储把金额改成 BIGINT 存分，除非表规模确实达到数亿行且经过基准测试验证收益。
  • 大表改精度/标度前先跑一遍 SUM 范围检查与 scale() 分布检查，确认不会溢出。
  • 输出层用 to_char 统一格式化金额，避免不同客户端（psql / JDBC / PDO）对 numeric 的默认展示不一致导致的对账困惑。

下一节

继续学习：浮点类型（REAL / DOUBLE PRECISION）

参考链接 ​