Re: Precision problems with float8

On Fri, 16 Nov 2001, Gabriel Fernandez wrote:

> Hi Stephan,
>
> Here I send a short script to show the problem when using the float8 in
> additions or substractions, etc.
>
> Just execute it and look at the output. It just creates a table
> ('prova') which has the 'amount' column with values with only two
> decimal places, but the sum(amount) has 10 decimal places.
>
> Just look at the output of the script and you'll see:
> In the set of data I send to you, you can see that the 10th decimal
> appears just when we add the last amount. So when it calculates 84193.26
> + 4346.44 the result is 88539.7000000001, given that the 84193.26  is
> the sum(amount) just before adding the last amount.
>
> I hope this will be enough to show the problem.

First, float8 is not an exact numeric.  I upped the output precision in my
copy of postgres to more digits, and I get:


            ?column?
---------------------------------
 select sum(amount) from prova ;
(1 row)

          sum
-----------------------
 88539.700000000098953
(1 row)

                      ?column?
----------------------------------------------------
 select sum(amount) from prova where code <> 3078 ;
(1 row)

          sum
-----------------------
 84193.260000000023865
(1 row)

                ?column?
-----------------------------------------
 select * from prova where code = 3078 ;
(1 row)

 code |        amount
------+-----------------------
 3078 | 4346.4399999999995998
(1 row)


----

Basically, you should not expect exact numeric answers from floats.
Anything you do should be to a reasonable number of significant digits
or decimal places and you should drop/ignore everything past that.