Postgres小数减法返回非预期0E-10结果的解决方案问询

根因说明 #

该现象是IEEE 754标准浮点数的共性问题，并非Postgres独有：十进制小数1.2627107407无法被二进制浮点数精确存储，实际存入的是和原值非常接近的近似值，两次解析生成的浮点数尾数位可能存在极细微差异，最终相减得到接近0的极小值而非完全等于0。

解决方法 #

• 优先使用numeric/decimal精确类型
  涉及需要高精度的小数计算场景（如财务核算、精密统计），避免使用real、double precision这类浮点类型，显式将数值转换为numeric类型后再计算：

  SELECT 1.2627107407::numeric - 1.2627107407::numeric AS test;
  

  该查询将返回精确的0结果。numeric类型基于十进制存储，只要小数位数在指定精度范围内，不会出现二进制浮点的精度丢失问题。
• 浮点场景下设定误差阈值处理
  如果必须使用浮点类型，可以根据业务精度要求设定可接受的误差范围（如1e-9），对计算结果做舍入或误差范围内判0：

  -- 舍入到指定小数位
  SELECT round(1.2627107407::double precision - 1.2627107407::double precision, 9) AS test;
  
  -- 误差范围内判0
  SELECT CASE WHEN abs(a - b) < 1e-9 THEN 0 ELSE a - b END AS test FROM your_table;
  

• 格式化输出屏蔽极小值
  仅需要解决显示问题时，可以通过格式化输出函数屏蔽极微小的误差值：

  SELECT to_char(1.2627107407::double precision - 1.2627107407::double precision, '9999999999.9999999999') AS test;
  

内容的提问来源于stack exchange，提问作者Mankind1023