如何在PostgreSQL中对两个NUMERIC值做除法并获取最大位数结果？

问题背景 #

按PostgreSQL 17的NUMERIC类型文档，未指定精度和小数位的NUMERIC是无约束列，不会强制转换输入的小数位数。但执行SELECT (1::numeric / 3::numeric);时，结果却是0.33333333333333333333，小数位被强制设为20，而且除法操作的文档里没提这个规则。把结果转成numeric(26,25)得到0.3333333333333333333300000，说明这不是显示问题。另外，只要有一个操作数指定的小数位>20（比如3::numeric(1000,50)），结果就会包含更多位数。文档说可指定的最大小数位是1000，但NUMERIC实际支持到16383。现在想知道：怎么对两个NUMERIC值做除法，才能拿到该类型支持的最大位数结果？

解决方案 #

有两种靠谱的方法能实现这个需求：

• 给至少一个操作数显式指定最大小数位
  PostgreSQL的NUMERIC除法会根据操作数的精度自动推导结果的精度，所以只要把参与运算的其中一个数转成numeric(16384,16383)（因为NUMERIC的精度=整数位+小数位，这里留1位整数位，剩下的16383位全给小数位，刚好是内部支持的上限），就能让除法结果保留最多小数位。示例：

  SELECT 1::numeric / 3::numeric(16384,16383);
  

  也可以两个操作数都指定成这个类型，效果一样：

  SELECT 1::numeric(16384,16383) / 3::numeric(16384,16383);
  

• 验证结果的精度
  要是想确认结果确实保留了最大位数，可以把结果转成numeric(16384,16383)看看：

  SELECT (1::numeric / 3::numeric(16384,16383))::numeric(16384,16383);
  

  这时候结果会是16383位的循环小数（0.333...333），不会像之前那样末尾补0（除非除法能除尽）。

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