PostgreSQL如何将double precision无损转换为numeric类型

你遇到的截断现象本质不是转换逻辑的问题，而是PostgreSQL默认浮点数输出精度配置导致的。double precision属于二进制浮点类型，本身是近似存储，默认配置下extra_float_digits参数为0，转文本仅输出15位有效数字，当小数部分过长时就会出现末位截断的表象。比如你举例的23.291400909423828，整数部分占2位，默认配置下仅保留13位小数，自然就会丢失最后两位的28。

解决步骤 #

• 第一步：调整当前会话的浮点数输出参数，输出double类型的全部有效位
  执行命令：
  SET extra_float_digits = 3;
  该参数仅对当前会话生效，不会影响其他业务连接
• 第二步：使用带USING子句的ALTER语句执行类型转换，确保转换时用完整精度的文本中转
  通用转换命令：

  ALTER TABLE 你的表名
  ALTER COLUMN 目标列名
  TYPE numeric USING (目标列名::text::numeric);
  

  如果需要指定numeric的精度标度，比如总位数20、小数位15，可以写为：

  ALTER TABLE 你的表名
  ALTER COLUMN 目标列名
  TYPE numeric(20,15) USING (目标列名::text::numeric(20,15));
  

验证方法 #

转换完成后，可以执行以下查询验证数据一致性：

-- 先设置精度参数
SET extra_float_digits = 3;
-- 对比转换前后的值（提前备份原double列即可对比）
SELECT 原double列, 新numeric列 FROM 你的表名 WHERE 原double列::text::numeric != 新numeric列;

如果查询无结果，说明所有数据都完整转换无丢失

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