PostgreSQL浮点运算和类型。

I have a Postgres-driven application which rounds quantities to the next (not nearest) 4th decimal place. So, 0.00341 becomes 0.0035.

我有一个postgres驱动的应用程序，该应用程序将批量发送到下一个(不是最近的)第4位小数。所以,0.00341变成0.0035。

In implementing it, I came upon a situation where 0.0012 even is being rounded up to 0.0013, even though it shouldn't; it's 0.0012 even. In fact, even ceil() agrees, at first glance:

在实现它的过程中，我遇到了一个0。0012甚至是0。0013的情况，尽管它不应该这样;这是0.0012。事实上，乍一看，甚至ceil()也同意:

postgres=> SELECT ceil(((0.0012) * 10000)) / 10000;
        ?column?        
------------------------
 0.00120000000000000000
(1 row)

We know that there is no arbitrary precision introduced:

我们知道没有任意精度的引入:

postgres=> SELECT (ceil(((0.0012) * 10000)) / 10000) = 0.0012;
 ?column? 
----------
 t
(1 row)

Yet, when the figure 0.0012 is arrived at by way of a computation, the situation changes:

然而，当图0.0012通过计算到达时，情况发生了变化:

postgres=> SELECT (12::double precision / 60) * 0.006;
 ?column? 
----------
   0.0012
(1 row)

postgres=> SELECT ((12::double precision / 60) * 0.006) = 0.0012;
 ?column? 
----------
 f
(1 row)

It would appear that the computed 0.0012 is greater than actual 0.0012:

由此可见，计算出的0.0012大于实际的0.0012:

postgres=> SELECT ((12::double precision / 60) * 0.006) > 0.0012;
 ?column? 
----------
 t
(1 row)

Predictably, this leads the rounding mechanism to round 0.0012 "up" to 0.0013, which is obviously wrong if the expression evaluates to 0.0012 even:

可以预见，这将导致舍入机制将舍入0.0012“up”到0.0013，如果表达式的值为0.0012，则显然是错误的:

postgres=> SELECT ceil(((12::double precision / 60) * 0.006) * 10000) / 10000;
 ?column? 
----------
   0.0013
(1 row)

So, clearly I'm missing something here about how the expression is evaluated and/or how the data types involved are cast. There's additional precision introduced that shouldn't be there.

因此，显然我在这里漏掉了一些关于表达式如何计算以及/或涉及的数据类型如何转换的内容。还有一些额外的精度不应该存在。

Any help would be appreciated!

如有任何帮助，我们将不胜感激!

1 个解决方案

#1

I see the error of my ways. This is a job for the numeric type in PostgreSQL, which is an arbitrary-precision type intended precisely to provide exact calculation.

我看到了我的错误。这是PostgreSQL中的数字类型的工作，它是一种任意精确的类型，目的是精确地提供精确的计算。

From http://www.postgresql.org/docs/9.5/static/datatype-numeric.html:

从http://www.postgresql.org/docs/9.5/static/datatype-numeric.html:

8.1.2. Arbitrary Precision Numbers

8.1.2。任意精度的数字

The type numeric can store numbers with a very large number of digits and perform calculations exactly. It is especially recommended for storing monetary amounts and other quantities where exactness is required. However, arithmetic on numeric values is very slow compared to the integer types, or to the floating-point types described in the next section.

数字类型可以存储大量数字的数字，并精确地执行计算。特别建议在需要精确的地方储存货币数量和其他数量。但是，与整数类型或下一节中描述的浮点类型相比，对数值的算术运算速度非常慢。

#1