postgres double precision [closed]
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 * 25 (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.
精彩评论