Take the 2-minute tour ×
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. It's 100% free, no registration required.

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

1 Answer 1

up vote 4 down vote accepted

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

Your Answer

 
discard

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.