1

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?

Improve this question
4
  • 1
    Does this answer your question? Is floating point math broken? Commented Feb 2, 2021 at 19:55
  • Is postgres type numeric supposed to use floating point? The documentation says it is precise and exact up to 16383 digits. Commented Feb 2, 2021 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, and cannot be represented precisely. Commented Feb 2, 2021 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. Commented Feb 2, 2021 at 22:18

1 Answer 1

Reset to default
2

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.

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.