7

I'm trying to SUM 8 million floating point (REAL) values with simple query like this:

SELECT SUM(metric) FROM metrics;

However, it returns very inaccurate result. It should return 137,586.77, but it returns 137,303 (283.77 difference)

Is there a way to force a query to be more precise?

1

3 Answers 3

8

According to the documentation of the REAL datatype http://www.postgresql.org/docs/9.2/static/datatype-numeric.html#DATATYPE-FLOAT it has a precision of 6 decimal digits. That's what you are seeing. If you need more precision you need to use double or a number with fixed precision.

Maybe casting the reals to something more precise and summing the result will give you what you need, eg:

SELECT SUM(metric::double) FROM metrics;
2
  • 3
    Thanks! It worked, though I had to use SELECT SUM(metric::DOUBLE PRECISION) FROM metrics; since my db didn't know about the "double" type. Mar 27, 2013 at 12:00
  • 1
    I agree - basically, if you really need precise sums and products and whatnot, use numeric instead. Mar 27, 2013 at 16:40
10

As per the postgresql documentation,

If you require exact storage and calculations (such as for monetary amounts), use the numeric type instead.

Since You've used real datatype, you can casting the real values to numeric. Then you can get results as you expected.

SELECT SUM(metric::NUMERIC) FROM metrics;
0
-6

SELECT SUM(metric::float) FROM metrics;

1
  • 2
    Hi, will you please add more details. Oct 25, 2017 at 8:42

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.