Imprecise math in postgres using type field numeric?

Question

Very strange issue I am noticing... This link says the numeric data type should be able to precisely handle 16383 digits after the decimal: https://www.postgresql.org/docs/10/datatype-numeric.html

So can someone plz explain to me why this function returns 9499.99999999999905:

(((60000::numeric / 50500 * 50500) - 50500) * (50500::numeric / 50500))::numeric

The correct answer is 9500.

When I use this function I get the right answer:

(((60000::numeric(20,11) / 50500 * 50500) - 50500) * (50500::numeric(20,11) / 50500))::numeric(20,11)

and this gives wrong answer:

(((60000::numeric(25,16) / 50500 * 50500) - 50500) * (50500::numeric(25,16) / 50500))::numeric(25,16) = 9499.9999999999990500

The odd thing is this same issue is happening on this website: https://web2.0calc.com/

if you paste the formula:

((60000.0000000 / 50500 * 50500) - 50500) * (50500.0000000 / 50500) = 9500.

But if I instead add an extra 0 to each of those:

((60000.00000000 / 50500 * 50500) - 50500) * (50500.00000000 / 50500) = 9499.99999999999999999999999999999999999999999999999999999999999905.

Even weirder, for both postgres and this website, if I break down the formula into two executions like this:

((60000.00000000 / 50500 * 50500) - 50500) = 9500
(50500.00000000 / 50500) = 1

9500 * 1 = 9500.

What the heck is going on here?

Does this answer your question? [Is floating point math broken?](https://stackoverflow.com/questions/588004/is-floating-point-math-broken) — Bergi, Feb 02 '21 at 19:55
Is postgres type numeric supposed to use floating point? The documentation says it is precise and exact up to 16383 digits. — Mr_E, Feb 02 '21 at 20:01
Maybe not exactly *floating*-point, and definitely decimal instead of binary, but still **limited**-precision. The fraction `200/101` that you're computing with [is infinite in decimal](https://dbfiddle.uk/?rdbms=postgres_13&fiddle=964c423c144127050e0652565be7afec), and cannot be represented precisely. — Bergi, Feb 02 '21 at 20:54
Yes you're right, I was thrown off by that website saying ((60000.00000000 / 50500 * 50500) - 50500) == 9500 when in fact it is not the case. Thank you. — Mr_E, Feb 02 '21 at 22:18

score 1 · Accepted Answer · answered Feb 03 '21 at 07:57

That you get the right answer with numeric(20,11) and the wrong one with numeric(25,16) is a coincidence: both will have rounding errors, because they calculate only to a limited precision, but in the first case the rounded result happens to be the correct one.

The same is the case for 60000.0000000 and 60000.00000000: they are interpreted as numeric values with different scale.

SELECT scale(60000.0000000), scale(60000.00000000);

 scale | scale 
-------+-------
     7 |     8
(1 row

The only thing that is not obvious is why you get such a bad scale when you cast to numeric without any scale or precision.

The scale of 60000::numeric is 0, and 50500 is also converted to a numeric with scale 0. Now if you divide two numeric values, the resulting scale is calculated by the function select_div_scale, and a comment there clarifies the matter:

/*
 * The result scale of a division isn't specified in any SQL standard. For
 * PostgreSQL we select a result scale that will give at least
 * NUMERIC_MIN_SIG_DIGITS significant digits, so that numeric gives a
 * result no less accurate than float8; but use a scale not less than
 * either input's display scale.
 */

NUMERIC_MIN_SIG_DIGITS has the value 16. The problem that this heuristic solves is that the scale of the division cannot be determined by looking at the scale of the arguments. So PostgreSQL chooses a value of 16, unless one of the arguments has a bigger scale. This avoids ending up with extremely large result values, unless someone explicitly asks for it.

Imprecise math in postgres using type field numeric?

1 Answers1