Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
up vote 5 down vote accepted

According to the documentation of the REAL datatype 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;
share|improve this answer
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. – Milovan Zogovic Mar 27 '13 at 12:00
I agree - basically, if you really need precise sums and products and whatnot, use numeric instead. – dezso Mar 27 '13 at 16:40

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;
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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