Postgres Pro
Downloads
Home   >   mailing lists

Thread: Precision when substracting two values with SQL

Precision when substracting two values with SQL

From
Juancho
Date:
Hello:
    I have a problem substracting two values whith SQL.  Please
consider this cases...

ciudadela=# select 0.2-0.2;
 ?column?
----------
        0
(1 row)

    That's OK for me,


ciudadela=# select 0.2-0.21;
       ?column?
----------------------
 -0.00999999999999998
(1 row)

    I was hoping -0.01,


ciudadela=# select 0.21-0.22;
 ?column?
----------
    -0.01
(1 row)

    Again, that's OK for me,


    My question is why is this behavior taking place (the second case),
 and how can I avoid it.
    Any suggestion would be really appreciated.
    Thanks,


Juan Manuel
Buenos Aires
Argentina

Re: Precision when substracting two values with SQL

From
Tim Ellis
Date:
> ciudadela=# select 0.2-0.2;
>         0
> ciudadela=# select 0.2-0.21;
>  -0.00999999999999998
> ciudadela=# select 0.21-0.22;
>     -0.01

I also get this on my Postgres 7.2.1, both Linux and Solaris.

--
Tim Ellis
Senior Database Architect
Gamet, Inc.

Re: Precision when substracting two values with SQL

From
Larry Rosenman
Date:
On Mon, 2002-08-12 at 15:24, Tim Ellis wrote:
> > ciudadela=# select 0.2-0.2;
> >         0
> > ciudadela=# select 0.2-0.21;
> >  -0.00999999999999998
> > ciudadela=# select 0.21-0.22;
> >     -0.01
>
> I also get this on my Postgres 7.2.1, both Linux and Solaris.
You are comparing floats.

This is a STANDARD problem with Base 10 Floating point.

LER

--
Larry Rosenman                     http://www.lerctr.org/~ler
Phone: +1 972-414-9812                 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

Re: Precision when substracting two values with SQL

From
Andrew Sullivan
Date:
On Mon, Aug 12, 2002 at 04:57:32PM -0300, Juancho wrote:
>
> ciudadela=# select 0.2-0.21;
>        ?column?
> ----------------------
>  -0.00999999999999998
> (1 row)
>
>     I was hoping -0.01,

Try

select (0.2-0.21)::numeric(12,2);

A

--
----
Andrew Sullivan                               87 Mowat Avenue
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M6K 3E3
                                         +1 416 646 3304 x110

Re: Precision when substracting two values with SQL

From
Juancho
Date:
>
>
>Try
>
>select (0.2-0.21)::numeric(12,2);
>
>A
>
>
>

Thank you, I will do this...

Re: Precision when substracting two values with SQL

From
Jean-Luc Lachance
Date:
I think you meant:

select 0.2:: numeric(12,2) - 0.21::numeric(12,2);

If you are not conviced try:

select (0.2-0.21)::numeric(40,20);


Andrew Sullivan wrote:
>
> On Mon, Aug 12, 2002 at 04:57:32PM -0300, Juancho wrote:
> >
> > ciudadela=# select 0.2-0.21;
> >        ?column?
> > ----------------------
> >  -0.00999999999999998
> > (1 row)
> >
> >       I was hoping -0.01,
>
> Try
>
> select (0.2-0.21)::numeric(12,2);
>
> A
>
> --
> ----
> Andrew Sullivan                               87 Mowat Avenue
> Liberty RMS                           Toronto, Ontario Canada
> <andrew@libertyrms.info>                              M6K 3E3
>                                          +1 416 646 3304 x110
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org