PostgreSQL浮点列求和有偏差：选类型转换还是修改表结构？

问题根源在于real是32位单精度浮点数，仅能提供约6-7位有效数字精度，多次累加（尤其是分组后再汇总的场景）会放大精度损失，导致不同求和路径结果不一致。而double precision（64位双精度浮点数）拥有15-17位有效数字，能大幅降低这类偏差。

方案选择：临时转换 vs 修改表结构 #

1. 每次求和时转换类型 #

• 适用场景：
  • 表数据量小，转换性能开销可忽略
  • 无表结构修改权限，或依赖该表的外部系统无法兼容类型变更
• 操作示例：

  -- 全表直接求和
  select sum(cast(work_res as double precision)) from work_data;
  
  -- 分组后汇总求和
  select sum(gd.work_res)
  from (
      select type, sum(cast(work_res as double precision)) as work_res
      from work_data
      group by type
    ) as gd;
  

• 优缺点：
  • 优点：无需改动表结构，灵活可控
  • 缺点：每次查询需手动写转换逻辑，容易遗漏；数据量极大时，频繁转换会带来微小性能损耗

2. 修改表结构为double precision #

• 适用场景：
  • 该列长期需要高精度聚合计算
  • 有权限修改表结构，且所有依赖业务兼容双精度类型
• 操作示例：

  -- 大表建议在低峰期执行，可包裹事务避免锁表影响业务
  alter table work_data alter column work_res type double precision;
  

• 优缺点：
  • 优点：一劳永逸，所有查询无需额外转换，彻底避免遗漏导致的精度问题
  • 缺点：大表执行该操作会触发排他锁，需评估业务影响；若有外部系统依赖real类型，需同步调整兼容

关于微小偏差的说明 #

即使使用double precision，依然可能存在极其微小的精度偏差——这是二进制浮点数的固有特性，比如你提到的0.(3)（1/3）这类十进制小数无法被二进制精确表示。但这种偏差通常在1e-15量级，针对你的业务场景（值范围(0,1]，求和结果约100），偏差完全可以忽略，不会影响实际业务判断。

如果需要绝对精确的计算（如金融场景），建议改用numeric类型（支持任意精度小数），但它的计算性能会略低于double precision。

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