Обсуждение: Precision problems with float8

Hi,

I send a week ago a mail and I have not get any answer.

The main thing I want to know is: If I use the numeric type instead of
float8 will it result in a slower overall performance or it won't be
significantly different ?

As I posted I have some problems when I execute round(number,precision)
and use it in arithmetical operations: look at this:

compta=> select import from apunts where assent = 4 ;

  import
---------
   9.38

AND THEN:

compta=> select import - round(import,2)::float8 as substraction from
apunts where assent = 4 ;

        substraction
---------------------------
-1.77635683940025e-15

(Of course, the initial 9.38 is the result of a previous calculation,
rounded with two decimals places, but the only thing you can see in the
value of the field is "9.38" ).

Does anyone know the reason ?

Thanks for your attention,

Gabi :-)

On Fri, 9 Nov 2001, Gabriel Fernandez wrote:

> Hi,
>
> I send a week ago a mail and I have not get any answer.
>
> The main thing I want to know is: If I use the numeric type instead of
> float8 will it result in a slower overall performance or it won't be
> significantly different ?

Yes, it's almost certainly slower than float8, but I don't know how
significant it is (especially since you're already doing numeric<->
double precision conversions I think)

> (Of course, the initial 9.38 is the result of a previous calculation,
> rounded with two decimals places, but the only thing you can see in the
> value of the field is "9.38" ).
>
> Does anyone know the reason ?

Can you send a test script that generates a broken row?  I'm not sure
how to diagnose it from the information given.

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.

Thanks,

Gabi :-)

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.