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.