Postgres中2个浮动值的总和以更高的精度为结果。
预期：
669.05 + 1.64 = 670.69
实际：
SELECT CAST(669.05 AS FLOAT) + CAST(1.64 AS FLOAT)
------------------
670.6899999999999
结果的精度高于预期。
具有不同输入集的相同操作的行为不同。
SELECT CAST(669.05 AS FLOAT) + CAST(1.63 AS FLOAT)
------------------
670.68
在这里，我通过找到存在问题的两个数字来减少问题陈述。
实际问题是，当我在整个桌子上这样做时，结果将很大，而更高的精确度（取决于值，我对精确射击的准确值/类型的值都没有解释），我们必须在应用程序级别处理规模。
示例查询
SELECT numeric_column_1/ CAST(numeric_column_2 AS FLOAT) FROM input_table;
注意：Float（53）的行为也相同。
The sum of 2 floating values in postgres gives the result with higher precision.
Expected:
669.05 + 1.64 = 670.69
Actual:
SELECT CAST(669.05 AS FLOAT) + CAST(1.64 AS FLOAT)
------------------
670.6899999999999
The result is having higher precision than expected.
The same operation with different set of inputs behaves differently.
SELECT CAST(669.05 AS FLOAT) + CAST(1.63 AS FLOAT)
------------------
670.68
Here I have reduced the problem statement by finding the 2 numbers for which the issue exists.
The actual problem is when I do this on a whole table the result would be very big with higher precisions (depending on the values, and I do not have an explanation for exactly what/kind of values the precision shoots up) and we had to handle the scale in the application level.
Example query
SELECT numeric_column_1/ CAST(numeric_column_2 AS FLOAT) FROM input_table;
Note: The behaviour is same for FLOAT(53) as well.
发布评论
评论（1）
根据PostgreSQL文档，Float使用不精确的精度。最好使用
十进制或数字，该数字支持精确的用户指定的精度。postgresql 中的浮点
数字类型
DB提议：在这里尝试
As per postgresql documentation, float uses inexact precision. Better to use
DECIMALorNUMERIC, which supports exact user specified precision.Floating-Point Types in PostgreSQL
Numeric Types
DB Fiddle: Try it here