postgres double precision [closed]

开发者_运维技巧 It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center. Closed 11 years ago.

select round(product_qty * 100) - product_qty as test, 
       id, product_qty 
from stock_move 
where product_id=63 
  and state='done' 
  and id=45058;

        test         |  id   | product_qty 
54.45                | 45058 |        0.55
(1 ligne)

select round(product_qty * 100) - (product_qty*100) as test, 
       id, 
       product_qty 
from stock_move 
where product_id=63 
  and state='done' 
  and id=45058;

        test         |  id   | product_qty 

 -7.105427357601e-15 | 45058 |        0.55
(1 ligne)

can somebody explain me these results?

---

Floating-point representations like double precision fields in pg are by their very nature subject to rounding errors. This paper gives a good mathematical introduction.

How you deal with these rounding errors depends on a bit on your application, and varies between:

• just living with them eg by sort of ignoring them - format that result as %.2f and the error will become invisible. Just make sure you understand all the consequences of that choice!!
• switching datatypes to something that by design is not subject to rounding issues - check out pg's money type and the exact numeric types.

The latter should be the preferred approach especially in contexts where your application handles money.

---

You're getting those results because product_qty*100 has different IEEE-754 binary representation than round(product_qty * 100):

without round : 100000001001011100000000000000000000000000000000000000000000001
with round    : 100000001001011100000000000000000000000000000000000000000000000

It's easy to see that 55 should be represented as 1,71875 * 2⁵ (as in with round), but due to floating point arithmetic product_qty*100 is slightly different.

If you want to still live with floating point number, then I recommend to read "What every computer scientist should know about floating-point arithmetic" paper, or just switch to numeric type with arbitrary precision.