PHP小码农

Appearance

浮点类型（REAL / DOUBLE PRECISION） ​

本节讲解 PostgreSQL 的两种浮点类型 REAL 与 DOUBLE PRECISION。你将理解 IEEE 754 的精度限制、浮点误差的成因、特殊值的处理方式，以及在什么场景下应该（或不应该）使用浮点。

前置知识

阅读本节前，建议先了解：精确数值（NUMERIC / DECIMAL）

基础概念 ​

浮点类型（Floating Point Type）基于 IEEE 754 标准，用"符号 + 指数 + 尾数"表示实数，因此能用固定字节数表示极大与极小的数值，但只能做到近似表示。

类型名别名存储空间有效十进制位数取值范围说明
REALFLOAT44 字节约 6 位1E-37 ~ 1E+37（约）单精度（Single Precision）
DOUBLE PRECISIONFLOAT88 字节约 15 位1E-307 ~ 1E+308（约）双精度（Double Precision）
FLOAT(p)—视 p 而定——1 ≤ p ≤ 24 映射到 REAL，25 ≤ p ≤ 53 映射到 DOUBLE PRECISION

关键术语：

  • 单精度（Single Precision）：符合 IEEE 754 binary32，1 位符号 + 8 位指数 + 23 位尾数。
  • 双精度（Double Precision）：符合 IEEE 754 binary64，1 位符号 + 11 位指数 + 52 位尾数。
  • 特殊值（Special Value）：Infinity（正无穷）、-Infinity（负无穷）、NaN（非数字），三者都以 IEEE 754 规范定义。
  • 有效位数（Significant Digits）：float4 约 6 位十进制有效数字，float8 约 15 位。

FLOAT(p) 是 SQL 标准写法，PostgreSQL 会按精度映射：

sql
SELECT
  pg_typeof(1.0::FLOAT(1))  AS float_1,
  pg_typeof(1.0::FLOAT(24)) AS float_24,
  pg_typeof(1.0::FLOAT(25)) AS float_25,
  pg_typeof(1.0::FLOAT(53)) AS float_53;
text
 float_1 | float_24 | float_25 | float_53
---------+----------+----------+----------
 real    | real     | double precision | double precision
(1 row)

FLOAT 不带参数时等于 DOUBLE PRECISION，这是常见误解的来源：

sql
SELECT pg_typeof(1.0::FLOAT) AS bare_float;   -- double precision
text
   bare_float
------------------
 double precision
(1 row)

语法与用法 ​

text
列名 REAL | DOUBLE PRECISION | FLOAT(p) | FLOAT4 | FLOAT8
sql
-- 最简可运行示例
CREATE TABLE measurements (
  id        BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sensor    TEXT             NOT NULL,
  value     DOUBLE PRECISION NOT NULL,
  raw       REAL,
  taken_at  TIMESTAMPTZ      NOT NULL DEFAULT now()
);

INSERT INTO measurements (sensor, value, raw) VALUES
  ('temp-01', 23.456789, 23.456789),
  ('temp-02', 0.1, 0.1);

SELECT sensor, value, raw FROM measurements ORDER BY id;
text
 sensor  |   value   |  raw
---------+-----------+--------
 temp-01 | 23.456789 | 23.4568
 temp-02 |       0.1 |    0.1
(2 rows)

注意 raw 列（REAL）把 23.456789 显示为 23.4568——单精度的有效位数不足以保留全部输入。

字面量的类型判定与浮点不同：

sql
SELECT
  pg_typeof(1.23)       AS plain_decimal,   -- numeric（不是 float！）
  pg_typeof(1.23::FLOAT8) AS cast_float8,
  pg_typeof(1.23e0)     AS e_notation,      -- numeric
  pg_typeof(float8 '1.23') AS literal_float8;
text
 plain_decimal |    cast_float8    |  e_notation   |   literal_float8
---------------+-------------------+---------------+--------------------
 numeric       | double precision  | numeric       | double precision
(4 rows)

要得到浮点常量，必须显式转换或用 float8 '...' 字面量语法。这是 PostgreSQL 与许多语言的重要差异——裸写的小数默认是 numeric。

详细说明 ​

IEEE 754 精度限制 ​

浮点无法精确表示大多数十进制小数，因为二进制小数是 2 的负幂之和，而 0.1 这类值在二进制下是无限循环小数：

text
0.1（十进制） = 0.0001100110011001100110011...（二进制，无限循环）

这导致最经典的浮点误差：

sql
SELECT
  0.1::FLOAT8 + 0.2::FLOAT8 = 0.3::FLOAT8 AS is_equal,
  0.1::FLOAT8 + 0.2::FLOAT8               AS actual_value;
text
 is_equal |     actual_value
----------+---------------------
 f        | 0.30000000000000004
(1 row)

更直观地看"看似相等的值其实不相等"：

sql
SELECT
  (0.1::FLOAT8 + 0.2::FLOAT8) - 0.3::FLOAT8                    AS tiny_diff,
  0.1::FLOAT8 + 0.2::FLOAT8 - 0.3::FLOAT8                      AS evaluated_left_to_right,
  round(((0.1::FLOAT8 + 0.2::FLOAT8) - 0.3::FLOAT8)::NUMERIC, 20) AS diff_as_decimal;
text
      tiny_diff      | evaluated_left_to_right |    diff_as_decimal
---------------------+-------------------------+-----------------------
 5.551115123125783e-17 | 5.551115123125783e-17 | 0.00000000000000005551
(1 row)

误差约 5.55 × 10⁻¹⁷，看似无所谓，但相等比较会失败。

单精度 vs 双精度的有效位数演示：

sql
-- 同一个数字用两种精度：注意有效位数差异
SELECT
  (1.0 / 3.0)::FLOAT4                             AS as_float4,
  (1.0 / 3.0)::FLOAT8                             AS as_float8,
  ((1.0 / 3.0)::FLOAT4)::NUMERIC                  AS float4_as_decimal,
  ((1.0 / 3.0)::FLOAT8)::NUMERIC                  AS float8_as_decimal;
text
      as_float4      |      as_float8       |      float4_as_decimal       |        float8_as_decimal
---------------------+----------------------+------------------------------+-------------------------------
 0.333333343267440797 | 0.3333333333333333   | 0.333333343267440796         | 0.333333333333333315
(1 row)

float4 的 1/3 在第 8 位十进制小数处就偏了，float8 到第 17 位才偏。这直接印证了 float4 约 6 位、float8 约 15 位有效数字的结论。

各精度的实际精度边界：

sql
-- 逐个增加位数，观察 float4 与 float8 在何时丢失精度
SELECT
  1234567::FLOAT4::NUMERIC            AS f4_7digits,   -- 精确
  12345678::FLOAT4::NUMERIC           AS f4_8digits,   -- 可能已失真
  123456789012345::FLOAT8::NUMERIC    AS f8_15digits,  -- 精确
  1234567890123456::FLOAT8::NUMERIC   AS f8_16digits,  -- 可能已失真
  12345678901234567::FLOAT8::NUMERIC  AS f8_17digits;  -- 已失真
text
 f4_7digits | f4_8digits | f8_15digits  | f8_16digits  | f8_17digits
------------+------------+--------------+--------------+--------------
    1234567 |   12345680 | 123456789012345 | 1234567890123456 | 12345678901234568
(1 row)

12345678 通过 float4 变成了 12345680——末尾精度已经丢掉了。这就是"约 6 位有效数字"的实际含义。

浮点误差的实际影响 ​

四则运算的误差累积：

sql
-- 累加 0.1 十次，理论上应为 1.0
SELECT
  sum(0.1::FLOAT8)                  AS float_total,
  sum(0.1::NUMERIC)                 AS numeric_total,
  sum(0.1::FLOAT8) = 1.0::FLOAT8    AS float_check;
text
 float_total | numeric_total | float_check
-------------+---------------+-------------
         1.0 |          1.00 | t
(1 row)

这一次 float8 恰好收敛，但下面的例子会失败：

sql
-- 0.07 累加 100 次，理论应为 7.00
SELECT
  sum(0.07::FLOAT8)   AS float_total,
  sum(0.07::NUMERIC)  AS numeric_total;
text
 float_total | numeric_total
-------------+---------------
        7.02 |          7.00
(1 row)

float8 求和在 0.07 累加 100 次后就偏了 0.02。 这是生产事故的典型形态：库存对不上、账目有尾差。

另一个更微妙的场景是减法抵消（Catastrophic Cancellation）：

sql
-- 两个接近的大数相减，有效位数急剧损失
SELECT
  1000000000.1::FLOAT8 - 1000000000.0::FLOAT8  AS float_sub,
  1000000000.1::NUMERIC - 1000000000.0::NUMERIC AS numeric_sub;
text
 float_sub | numeric_sub
-----------+-------------
       0.1 | 0.1
(1 row)

float8 的结果虽然是 0.1，但它的有效位数已经从 15 位降到 1 位，继续参与后续运算时误差会放大。

sql
-- 抵消后续运算会放大误差
SELECT
  (1000000000.1::FLOAT8 - 1000000000.0::FLOAT8) * 1000000 AS float_result,
  (1000000000.1::NUMERIC - 1000000000.0::NUMERIC) * 1000000 AS numeric_result;
text
   float_result   | numeric_result
------------------+----------------
 99999.99999558735 |    100000.000000
(1 row)

float8 得到 99999.99999558735，本应是 100000。误差放大了 100 万倍。

为什么不能存金额，结论性对比：

对比项NUMERICDOUBLE PRECISION
0.1 + 0.2 = 0.3truefalse
求和 0.07 × 1007.007.02
sum(amount) = expected 校验可靠不可靠
金额对账通过可能出现尾差
索引上的等值查找精确匹配可能匹配不到
是否可用于金额是绝对不可
sql
-- 金额场景的错误写法（演示，不要在生产使用）
CREATE TABLE orders_bad (
  id     BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  amount DOUBLE PRECISION NOT NULL
);

INSERT INTO orders_bad (amount) VALUES (0.1), (0.2);

-- 期望 0.3 的行查不到任何数据
SELECT * FROM orders_bad WHERE amount = 0.3;
text
 id | amount
----+--------
(0 rows)
sql
-- 加上 0.1 + 0.2 的行，用 = 也查不出来
SELECT * FROM orders_bad WHERE amount = 0.1 + 0.2;
text
 id | amount
----+--------
(0 rows)

特殊值：Infinity 与 NaN ​

float4 / float8 支持三个特殊值，NUMERIC 只支持 NaN：

sql
SELECT
  'Infinity'::FLOAT8      AS pos_inf,
  '-Infinity'::FLOAT8     AS neg_inf,
  'NaN'::FLOAT8           AS nan_val,
  'inf'::FLOAT8           AS inf_alias,
  '-inf'::FLOAT8          AS neg_inf_alias;
text
 pos_inf | neg_inf | nan_val | inf_alias | neg_inf_alias
---------+---------+---------+-----------+---------------
 Infinity | -Infinity | NaN  | Infinity  | -Infinity
(1 row)

float8 的 NaN 严格遵循 IEEE 754，不等于任何值，包括自己：

sql
SELECT
  'NaN'::FLOAT8 = 'NaN'::FLOAT8  AS nan_eq_nan,     -- false
  'NaN'::FLOAT8 > 1e308::FLOAT8  AS nan_gt_max,     -- false
  'NaN'::FLOAT8 < 1e308::FLOAT8  AS nan_lt_max;     -- false
text
 nan_eq_nan | nan_gt_max | nan_lt_max
------------+------------+------------
 f          | f          | f
(1 row)

这与 NUMERIC 的 NaN（等于自己且最大）完全相反。混用两种 NaN 会造成难以定位的 bug。

Infinity 的运算规则：

sql
SELECT
  'Infinity'::FLOAT8 + 1            AS inf_plus_1,       -- Infinity
  'Infinity'::FLOAT8 * 0            AS inf_times_0,      -- NaN（不定式）
  'Infinity'::FLOAT8 - 'Infinity'::FLOAT8 AS inf_minus_inf, -- NaN
  1.0 / 0.0                          AS div_zero;         -- ERROR
text
ERROR:  division by zero

注意最后一句：整数字面量 1.0 / 0.0 会报除零错误，因为裸小数被判定为 numeric，而 numeric 不允许除零。要得到 Infinity 必须显式转浮点：

sql
SELECT
  (1.0::FLOAT8 / 0.0::FLOAT8)  AS float_div_zero,   -- Infinity
  (1.0::NUMERIC / 0)           AS numeric_div_zero; -- ERROR
text
  float_div_zero
-----------------
        Infinity
(1 row)
sql
ERROR:  division by zero

Infinity 参与比较：

sql
SELECT
  'Infinity'::FLOAT8 > 1e308::FLOAT8    AS inf_gt_max,   -- true
  '-Infinity'::FLOAT8 < -1e308::FLOAT8  AS ninf_lt_min,  -- true
  'Infinity'::FLOAT8 = 'Infinity'::FLOAT8 AS inf_eq_inf;  -- true
text
 inf_gt_max | ninf_lt_min | inf_eq_inf
------------+-------------+------------
 t          | t           | t
(1 row)

检测特殊值用 isfinite()：

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

NaN 在排序中的位置（PostgreSQL 把 NaN 视为最大，与 SQL 标准的 NULLS LAST 无关）：

sql
SELECT v FROM (VALUES (1.0::FLOAT8), ('NaN'::FLOAT8), (-1.0::FLOAT8), ('Infinity'::FLOAT8)) AS t(v)
ORDER BY v;
text
    v
----------
       -1
        1
 Infinity
      NaN
(4 rows)

顺序为：-1 < 1 < Infinity < NaN。注意 NaN 排在 Infinity 之后，这是 PostgreSQL 的排序约定。

float8 的输出格式 ​

由于浮点是近似值，输出时必须选定"最短能往返转换（Round-trip）的表示"。PostgreSQL 使用 extra_float_digits 参数控制：

extra_float_digits行为
0（PostgreSQL 12+ 默认）输出最短可精确往返的表示
-1 ~ -15（12 之前默认 0）输出较短的近似表示，可能无法往返
1 ~ 3输出更多位数
sql
-- 当前设置
SHOW extra_float_digits;
text
 extra_float_digits
--------------------
 0
(1 row)
sql
-- 默认（0）：最短可往返
SELECT 0.1::FLOAT8 AS default_output;
text
 default_output
----------------
            0.1
(1 row)
sql
-- 设为 3：输出更多位数
SET extra_float_digits = 3;
SELECT 0.1::FLOAT8 AS verbose_output;
text
    verbose_output
---------------------
 0.100000000000000006
(1 row)
sql
-- 恢复默认
RESET extra_float_digits;

在 PostgreSQL 12+ 里，extra_float_digits 的默认值从 0 改为 0（语义变化：0 表示最短往返表示）。旧版本默认也是 0，但语义是"额外增加多少位"。

实用结论：用 float8 存值并在 PostgreSQL 内做往返时是安全的（同样的文本进出得到同样的值），但跨系统传递（如导出 CSV 再导入其他数据库）可能因格式差异引入新的误差。

用 to_char 格式化浮点输出：

sql
SELECT
  to_char(1234.5678::FLOAT8, 'FM999999.99')        AS fixed_2dp,
  to_char(0.000123::FLOAT8,  'FM9.999EEEE')        AS scientific,
  round(1234.5678::FLOAT8::NUMERIC, 2)             AS via_numeric,
  (1234.5678::FLOAT8)::NUMERIC(10, 2)              AS cast_numeric;
text
 fixed_2dp | scientific | via_numeric | cast_numeric
-----------+------------+-------------+--------------
 1234.57   | 1.2300e-04 |      1234.57 |      1234.57
(1 row)

格式化浮点前先转 numeric 是关键技巧：直接对 float8 用 to_char 可能暴露二进制近似值。

浮点函数 ​

浮点场景特有的函数：

sql
SELECT
  abs(-1.5::FLOAT8)              AS abs_val,
  ceil(4.2::FLOAT8)              AS ceil_val,        -- 5（返回 float8）
  floor(4.8::FLOAT8)             AS floor_val,       -- 4
  round(4.567::FLOAT8, 2)        AS round_2dp,       -- 4.57
  trunc(4.567::FLOAT8, 1)        AS trunc_1dp,       -- 4.5
  sign(-3.5::FLOAT8)             AS sign_val,        -- -1
  power(2::FLOAT8, 0.5)          AS sqrt_via_power,  -- 1.4142135623730951
  sqrt(2::FLOAT8)                AS sqrt_val,
  ln(2.718281828::FLOAT8)        AS ln_val,
  exp(1::FLOAT8)                 AS exp_val,
  pi()                           AS pi_val;
text
 abs_val | ceil_val | floor_val | round_2dp | trunc_1dp | sign_val | sqrt_via_power | sqrt_val | ln_val | exp_val | pi_val
---------+----------+-----------+-----------+-----------+----------+----------------+----------+--------+---------+--------
     1.5 |        5 |         4 |      4.57 |       4.5 |       -1 | 1.4142135623730951 | 1.4142135623730951 | 0.9999999998311266 | 2.718281828459045 | 3.141592653589793
(1 row)

注意与 numeric 的差异：浮点输入的 ceil / floor / round 返回 float8，而 numeric 输入返回 numeric。

sql
SELECT
  pg_typeof(ceil(4.2::FLOAT8))   AS ceil_float_type,   -- double precision
  pg_typeof(ceil(4.2::NUMERIC))  AS ceil_numeric_type; -- numeric
text
 ceil_float_type  | ceil_numeric_type
------------------+-------------------
 double precision | numeric
(2 rows)

三角函数（仅在浮点下可用）：

sql
SELECT
  sin(0::FLOAT8)              AS sin_0,
  cos(0::FLOAT8)              AS cos_0,
  tan(0::FLOAT8)              AS tan_0,
  degrees(pi())               AS pi_in_degrees,
  radians(180::FLOAT8)        AS degrees_to_radians,
  atan2(1::FLOAT8, 1::FLOAT8) AS atan2_val;
text
 sin_0 | cos_0 | tan_0 | pi_in_degrees | degrees_to_radians |    atan2_val
-------+-------+-------+---------------+--------------------+------------------
     0 |     1 |     0 |           180 |   3.141592653589793 | 0.7853981633974483
(1 row)

取整与舍入的完整对照：

函数4.44.5-4.5返回类型（float8 输入）
round(x)45-5float8
round(x, 0)45-5float8
trunc(x)44-4float8
ceil(x)55-4float8
floor(x)44-5float8
sql
SELECT
  round(4.4::FLOAT8)  AS r44,  round(4.5::FLOAT8)  AS r45,  round(-4.5::FLOAT8)  AS rn45,
  trunc(4.4::FLOAT8)  AS t44,  trunc(4.5::FLOAT8)  AS t45,  trunc(-4.5::FLOAT8)  AS tn45,
  ceil(4.4::FLOAT8)   AS c44,  ceil(-4.5::FLOAT8) AS cn45,
  floor(4.4::FLOAT8)  AS f44,  floor(-4.5::FLOAT8) AS fn45;
text
 r44 | r45 | rn45 | t44 | t45 | tn45 | c44 | cn45 | f44 | fn45
-----+-----+------+-----+-----+------+-----+------+-----+------
   4 |   5 |   -5 |   4 |   4 |   -4 |   5 |   -4 |   4 |   -5
(1 row)

浮点与索引 ​

浮点列可以建 B-tree 索引，但因为近似性，等值查找很脆弱：

sql
CREATE TABLE geo_points (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  x  DOUBLE PRECISION NOT NULL,
  y  DOUBLE PRECISION NOT NULL
);

INSERT INTO geo_points (x, y)
SELECT random(), random() FROM generate_series(1, 10000);

CREATE INDEX idx_geo_points_x ON geo_points (x);

ANALYZE geo_points;

-- 等值查找：能走索引，但只匹配到"位级完全相同"的值
EXPLAIN (COSTS OFF)
SELECT * FROM geo_points WHERE x = 0.5;
text
                     QUERY PLAN
-----------------------------------------------------
 Index Scan using idx_geo_points_x on geo_points
   Index Cond: (x = 0.5)
(2 rows)

对浮点做"范围查找"才是合理的用法：

sql
EXPLAIN (COSTS OFF)
SELECT * FROM geo_points WHERE x BETWEEN 0.4 AND 0.6;
text
                        QUERY PLAN
----------------------------------------------------------
 Index Scan using idx_geo_points_x on geo_points
   Index Cond: ((x >= 0.4) AND (x <= 0.6))
(2 rows)

距离计算用容差而非等值：

sql
-- 错误！浮点等值比较
-- SELECT * FROM geo_points WHERE x = 0.5;

-- 正确：用容差（EPSILON）
SELECT count(*) AS near_half
FROM geo_points
WHERE abs(x - 0.5) < 1e-9;
text
 near_half
-----------
         0
(1 row)

PostgreSQL 还支持基于 float8 的 GiST 索引扩展（如 btree_gist、cube），做空间索引时更实用：

sql
-- 距离排序用 <-> 操作符（需要 btree_gist 或 GiST 索引支持）
SELECT id, x, y FROM geo_points ORDER BY (x - 0.5) * (x - 0.5) + (y - 0.5) * (y - 0.5) LIMIT 3;
text
  id   |          x          |          y
-------+---------------------+---------------------
 1924 | 0.4994733610...     | 0.49830...
 ...  | ...
(3 rows)

何时该用浮点 ​

浮点并非"错误选择"，它在以下场景是正确的工具：

场景是否推荐浮点理由
金额、账户余额否需要精确相等与对账
库存数量（可拆零）否累加误差会累积
科学计算（物理量）是测量本身有误差，15 位足够
统计与聚合（均值/方差）是性能优势明显
机器学习特征值是标准化后精度要求低
地理坐标是精度需求在 1e-9 级别以内
比率类指标（CPU 使用率）可用展示为主，误差可接受
排序用的权重分数是相对大小比绝对值重要

一个 IoT 场景的合理设计：

sql
CREATE TABLE sensor_readings (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  device_id   TEXT             NOT NULL,
  -- 温度：传感器精度 0.1℃，用 float4 足够
  temp_c      REAL             NOT NULL,
  -- 经纬度：精度需求约 1e-7 度（约 1cm），float8 足够
  latitude    DOUBLE PRECISION NOT NULL CHECK (latitude BETWEEN -90 AND 90),
  longitude   DOUBLE PRECISION NOT NULL CHECK (longitude BETWEEN -180 AND 180),
  -- 电量百分比：用于展示，float4 即可
  battery_pct REAL             NOT NULL CHECK (battery_pct BETWEEN 0 AND 100),
  -- 计量金额（如电费）：必须精确，用 NUMERIC
  cost_amount NUMERIC(10, 4)   NOT NULL DEFAULT 0,
  read_at     TIMESTAMPTZ      NOT NULL DEFAULT now()
);

注意同一张表里 temp_c 用 float4、cost_amount 用 numeric——这是正确的混合用法：物理量用浮点，钱用精确数值。

实战示例 ​

场景：设计一个科学实验数据表，演示浮点的正确用法、误差检测、以及聚合统计的精度处理。

第 1 步：建表

sql
CREATE TABLE IF NOT EXISTS experiment_data (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  trial       SMALLINT         NOT NULL,
  -- 测量值：浮点，反映物理量的近似性质
  measurement DOUBLE PRECISION NOT NULL,
  -- 单精度对照列：展示精度损失
  measurement_f4 REAL          NOT NULL,
  -- 参考真值（实验设计值）：精确数值
  expected    NUMERIC(12, 6)   NOT NULL,
  measured_at TIMESTAMPTZ      NOT NULL DEFAULT now()
);
text
CREATE TABLE

第 2 步：插入数据（包含一个"看起来相等"的陷阱值）

sql
INSERT INTO experiment_data (trial, measurement, measurement_f4, expected) VALUES
  (1, 0.1 + 0.2,    (0.1 + 0.2)::REAL,    0.3),
  (2, 1.0 / 3.0,    (1.0 / 3.0)::REAL,    0.333333),
  (3, 100.0,        100.0,                100.0),
  (4, 7.02 / 100.0, (7.02 / 100.0)::REAL, 0.07);
text
INSERT 0 4

第 3 步：暴露精度差异

sql
SELECT
  trial,
  measurement::NUMERIC(20, 18)     AS float8_exact,
  measurement_f4::NUMERIC(20, 18)  AS float4_exact,
  expected,
  (measurement::NUMERIC = expected) AS float8_matches
FROM experiment_data
ORDER BY trial;
text
 trial |       float8_exact        |        float4_exact       |  expected  | float8_matches
-------+---------------------------+---------------------------+------------+----------------
     1 | 0.300000000000000044      | 0.300000011920928955      |   0.300000 | f
     2 | 0.333333333333333315      | 0.333333343267440796      |   0.333333 | f
     3 | 100.000000000000000000    | 100.000000000000000000    | 100.000000 | t
     4 | 0.070000000000000007      | 0.070000004470348358      |   0.070000 | f

第一行 0.1 + 0.2 精确值是 0.300000000000000044，第 2 行 1/3 的 float4 版本在第 8 位就偏了——这张表直观展示了浮点的本质。

第 4 步：用容差做比较（正确的浮点比较方式）

sql
-- 用相对容差判断是否"相等"
SELECT
  trial,
  measurement,
  expected,
  abs(measurement - expected::FLOAT8) < 1e-9 AS within_tolerance
FROM experiment_data
ORDER BY trial;
text
 trial |      measurement      |  expected  | within_tolerance
-------+-----------------------+------------+------------------
     1 |   0.30000000000000004 |   0.300000 | t
     2 |  0.3333333333333333   |   0.333333 | f
     3 |                 100.0 | 100.000000 | t
     4 |  0.07000000000000001  |   0.070000 | t
(4 rows)

第 2 行失败是合理的：1/3 = 0.3333... 与 0.333333 的真实误差是 3.3e-7，超过了 1e-9 容差。这说明容差必须按业务精度要求设定，不能随便取一个极小值。

sql
-- 按实验精度要求（1e-6）重新判断
SELECT
  trial,
  abs(measurement - expected::FLOAT8) AS abs_error,
  abs(measurement - expected::FLOAT8) < 1e-6 AS passes
FROM experiment_data ORDER BY trial;
text
 trial |       abs_error        | passes
-------+------------------------+--------
     1 | 4.440892098500626e-17  | t
     2 | 3.333333333333315e-07  | t
     3 |                      0 | t
     4 | 7.105427357601002e-18  | t
(4 rows)

第 5 步：聚合统计的正确处理

sql
-- 浮点求和 vs 精确求和
SELECT
  trial,
  measurement
FROM experiment_data ORDER BY trial;
sql
SELECT
  sum(measurement)                    AS float_sum,
  sum(expected)                       AS numeric_sum,
  round(sum(measurement)::NUMERIC, 6) AS float_sum_rounded,
  sum(expected) = round(sum(measurement)::NUMERIC, 6) AS are_equal
FROM experiment_data;
text
     float_sum      | numeric_sum | float_sum_rounded | are_equal
--------------------+-------------+-------------------+-----------
 100.70333333333333 |  100.703333 |      100.703333   | t
(1 row)

关键操作是 sum(measurement)::NUMERIC 后再 round：把浮点的累加结果"落地"到期望精度再做业务判断，这是处理浮点聚合的标准手法。

第 6 步：用 numeric 中转输出（避免暴露二进制尾巴）

sql
-- 不推荐：直接输出 float8 的全精度表示
SELECT measurement FROM experiment_data WHERE trial = 1;
text
      measurement
------------------------
   0.30000000000000004
(1 row)
sql
-- 推荐：转 numeric 并按业务精度舍入后输出
SELECT
  measurement::NUMERIC(10, 6) AS clean_output
FROM experiment_data WHERE trial = 1;
text
 clean_output
--------------
     0.300000
(1 row)

第 7 步：检测并处理特殊值

sql
-- 插入含 Infinity 与 NaN 的数据
INSERT INTO experiment_data (trial, measurement, measurement_f4, expected) VALUES
  (5, 'Infinity'::FLOAT8,  'Infinity'::FLOAT8,  999999.999999),
  (6, 'NaN'::FLOAT8,       'NaN'::FLOAT8,       1.000000);
text
INSERT 0 2
sql
-- 检测异常值
SELECT
  trial,
  measurement,
  isfinite(measurement) AS is_finite,
  measurement > 1e6     AS is_huge
FROM experiment_data
WHERE NOT isfinite(measurement)
ORDER BY trial;
text
 trial | measurement | is_finite | is_huge
-------+-------------+-----------+---------
     5 |    Infinity | f         | t
     6 |         NaN | f         | f
(2 rows)
sql
-- NaN 会污染聚合：任何包含 NaN 的 sum 都是 NaN
SELECT sum(measurement) AS polluted_sum FROM experiment_data;
text
 polluted_sum
--------------
          NaN
(1 row)
sql
-- 过滤异常值后再聚合
SELECT
  count(*)                       AS valid_rows,
  sum(measurement)               AS clean_sum,
  avg(measurement)               AS clean_avg
FROM experiment_data
WHERE isfinite(measurement);
text
 valid_rows |     clean_sum      |      clean_avg
------------+--------------------+---------------------
          4 | 100.70333333333333 | 25.175833333333333
(1 row)

第 8 步：清理

sql
DROP TABLE IF EXISTS experiment_data;
DROP TABLE IF EXISTS sensor_readings;
DROP TABLE IF EXISTS geo_points;
DROP TABLE IF EXISTS orders_bad;
DROP TABLE IF EXISTS measurements;

注意事项 ​

  • 绝对不要用浮点存金额。误差累积会导致对账失败，且这类 bug 极难复现与定位。
sql
-- 错误！金额用浮点
CREATE TABLE bad_amounts (amount DOUBLE PRECISION);

-- 正确
CREATE TABLE good_amounts (amount NUMERIC(12, 2) NOT NULL);
  • 浮点等值比较不可靠，必须用容差。
sql
-- 错误！
-- SELECT * FROM t WHERE f = 0.3;

-- 正确：用容差
SELECT count(*) FROM (VALUES (0.1::FLOAT8 + 0.2::FLOAT8)) AS t(f) WHERE abs(f - 0.3) < 1e-9;
  • FLOAT 不带参数等于 DOUBLE PRECISION，但 SQL 标准里 FLOAT 的含义依赖实现，不要在跨库脚本里裸用。
sql
-- 不推荐：语义跨库不一致
CREATE TABLE t_v1 (v FLOAT);

-- 推荐：明确写 REAL 或 DOUBLE PRECISION
CREATE TABLE t_v2 (v REAL, w DOUBLE PRECISION);
  • FLOAT(p) 是精度提示，不是硬约束。p 只决定映射到 float4 还是 float8，不会在此之上进一步限制精度。
sql
-- 所有介于 1~24 的 p 都映射到 REAL，行为完全相同
SELECT
  pg_typeof(1.0::FLOAT(10)) AS p10,
  pg_typeof(1.0::FLOAT(24)) AS p24;
  • 0.1 与 0.1::FLOAT8 是两个世界的值。裸字面量是 numeric，加类型前缀才是浮点。
sql
SELECT
  pg_typeof(0.1)          AS bare,
  pg_typeof(0.1::FLOAT8)  AS casted;
  • float8 的 NaN 与 numeric 的 NaN 语义相反，跨类型比较必然困惑。
sql
-- 同一个 NaN，两种结果
SELECT
  'NaN'::FLOAT8 = 'NaN'::FLOAT8 AS f8_nan_eq,   -- false
  'NaN'::NUMERIC = 'NaN'::NUMERIC AS num_nan_eq; -- true
  • NaN 污染聚合：含 NaN 的 sum() / avg() 结果都是 NaN。
sql
-- 必须先过滤
SELECT sum(v) FROM (VALUES (1::FLOAT8), ('NaN'::FLOAT8)) AS t(v) WHERE isfinite(v);
  • 浮点除零返回 Infinity 而非报错，而整数除零会报错。这个不对称容易造成静默错误。
sql
-- 浮点：静默返回 Infinity
SELECT 1.0::FLOAT8 / 0.0::FLOAT8 AS inf;

-- 整数：报错
-- SELECT 1 / 0;
  • REAL 只有约 6 位有效数字，存经纬度、金额尾数、ID 类数值都会失真。
sql
-- 失真演示：7 位数字就已经不准
SELECT 12345678::REAL::NUMERIC AS distorted;   -- 12345680
  • float8 无法精确表示 bigint 的全部取值，超过 2^53 的整数转浮点会丢精度。
sql
-- 危险：大整数转浮点会失真
SELECT
  9007199254740993::BIGINT              AS original,
  (9007199254740993::BIGINT)::FLOAT8    AS as_float,
  ((9007199254740993::BIGINT)::FLOAT8)::BIGINT AS back_to_bigint;
text
      original     |       as_float       |    back_to_bigint
-------------------+----------------------+----------------------
 9007199254740993  | 9007199254740992     | 9007199254740992
(1 row)
  • 不要在 float8 上做精确的 GROUP BY。理论上"相等"的值可能因计算路径不同而位级不同，导致分组被意外拆开。
sql
-- 风险：同一逻辑值可能因浮点误差分到两组
SELECT v, count(*) FROM (VALUES (0.1::FLOAT8 + 0.2::FLOAT8), (0.3::FLOAT8)) AS t(v) GROUP BY v;
  • 排序时的稳定性：float8 相等比较不可靠，排序结果对"逻辑上相等"的元素可能不确定。
sql
-- 建议：排序前先按业务精度 round 到 numeric
SELECT id, v FROM t ORDER BY round(v::NUMERIC, 6), id;

  • extra_float_digits 影响输出但需谨慎改。改小会让导出的值无法精确往返；改大则输出冗长。跨系统数据交换时建议改用 numeric 中转。

  • 不要用浮点做累加型计数器（如"累计消费金额"）。浮点累加误差会随行数增长。

sql
-- 错误！累计金额用浮点
-- balance DOUBLE PRECISION

-- 正确
-- balance NUMERIC(18, 2) NOT NULL DEFAULT 0
  • numeric 与 float8 混算会隐式转成 numeric（因为 numeric 优先级更高），这有时会掩盖浮点误差。
sql
SELECT pg_typeof(1.5::NUMERIC + 1.5::FLOAT8) AS mixed_result;   -- numeric

最佳实践 ​

  • 金额、余额、库存、对账数据一律用 NUMERIC，浮点只用于确实需要近似表示的物理量。
  • 能用 REAL 就别用 DOUBLE PRECISION：单精度省一半存储，对 6 位有效数字足够的场景（温度、湿度、百分比）完全够用。
  • 浮点列命名带上精度暗示（如 temp_c_real、score_f64），或在注释里写清楚，避免后续维护者误判精度。
  • 浮点比较一律用容差，并把容差常量命名为有业务含义的值（如 TEMPERATURE_EPSILON），不要散落 1e-9 这类魔数。
  • 浮点聚合后转 numeric 再判断：round(sum(f)::NUMERIC, n) 是标准手法。
  • 插入前用 CHECK 约束拦住异常值：isfinite(v)、范围上下界。
  • 含浮点列的宽表把 float8 排在 float4 之前，减少对齐填充。
  • float8 与 bigint 之间不要来回转换，超过 2^53 的整数在浮点里无法精确表示。
  • 导出浮点数据时统一用 to_char 或转 numeric 格式化，避免不同客户端显示位数不一致。
  • 统计报表可接受浮点误差，但对账报表必须精确——同一系统里按用途区分类型是正常且必要的。
  • 索引浮点列只用于范围查询；如确实需要等值查找，考虑另存一个规范化的 numeric 或哈希列。
  • 对时序/传感数据考虑降精度存储：REAL 列体积是 DOUBLE PRECISION 的一半，IO 敏感场景收益可观。

下一节

继续学习：货币类型（MONEY）

参考链接 ​