Re: [HACKERS] 123.45 - 123 = 0.45

Bruce Momjian <maillist(at)candle(dot)pha(dot)pa(dot)us> writes:
> [ 123.45 - 123.00 = 0.450000000000003 ]

> Now constants are automatically promoted to float8, so I expected the
> same results, but what is going on here?

Plain old, garden-variety, floating point roundoff error. Do the
same calculation in any other program and you'll get the same result
(on the same hardware anyway), if the other program insists on showing
16 digits of precision.

IEEE 64-bit floats only have about 17 decimal digits of accuracy.
So float8(123.45) is good to about 14 digits after the decimal point.
Subtract off the 123, and print what's left with 16 digits, and
by golly you find out that the original number wasn't exactly 123.45,
just an approximation to it. Along about the 15th digit after the
decimal point, you start finding crud.

In short: no surprises here for anyone who's used float math for any
length of time.

Sooner or later we ought to try to implement true fixed-point
arbitrary-precision numeric data types per the SQL spec. That'll be
a lot slower than hardware float math, but its roundoff properties will
be less surprising to novices.

regards, tom lane