PostgreSQL floating point arithmetic and types

Question

I have a Postgres-driven application which rounds quantities to the next (not nearest) 4th decimal place. So, 0.00341 becomes 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:

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:

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:

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:

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!

It's in no way specific to Postgres. I get the same result in a number of programming languages, so, obviously I am missing something about types and precision. — Alex Balashov
– Alex Balashov, Commented Apr 27, 2016 at 22:52
PostgreSQL (and any others) does not use its own arithmetic but uses CPU/OS instead. There are a lot of info about limitations of floating points arithmetic usage. Specify the appropriate precision/scale to get the properly results. For example, SELECT ((12::numeric(16,8) / 60) * 0.006) = 0.0012; returns t — Abelisto
– Abelisto, Commented Apr 27, 2016 at 23:39

Alex Balashov · Accepted Answer · 2016-04-27 23:39:07Z

3

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.

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

8.1.2. Arbitrary Precision Numbers

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.

answered Apr 27, 2016 at 23:39

Alex Balashov

3,4686 gold badges30 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL floating point arithmetic and types

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related