Re: Floating point error

Tom Duffey <tduffey(at)trillitech(dot)com> wrote:

> CREATE TABLE test (
>     id INTEGER PRIMARY KEY,
>     value REAL NOT NULL
> );
>
> INSERT INTO test (id, value) VALUES (1, 10.3884573), (2, 10.3885);
> SELECT * FROM test;
>
> id |  value
> ----+---------
>   1 | 10.3885
>   2 | 10.3885
> (2 rows)
>
> At this point you would think you have two equal values.

At this point, try this:

select * from test where value = '10.3885';

 id |  value
----+---------
  2 | 10.3885
(1 row)

Now try this:

select * from test where value = 10.3885;
 id | value
----+-------
(0 rows)

Always remember that floating point types are *approximate* data
types; equality often does not behave as you might expect.  You're
probably aware of the below issues, but just in case:

select '99999999'::real = ('99999999'::real + '1'::real);
 ?column?
----------
 t
(1 row)

select '.1'::real::float;
      float8       
-------------------
 0.100000001490116
(1 row)

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company