Re: Floating point error

Tom Duffey <tduffey@trillitech.com> wrote:=0A=0A> CREATE TABLE test (=0A>=
=A0=A0=A0=A0 id INTEGER PRIMARY KEY,=0A>=A0=A0=A0=A0 value REAL NOT NULL=0A=
> ); =0A>=0A> INSERT INTO test (id, value) VALUES (1, 10.3884573), (2, 10.3=
885);=0A> SELECT * FROM test;=0A>=0A> id |=A0 value =0A> ----+---------=0A>=
=A0=A0 1 | 10.3885=0A>=A0=A0 2 | 10.3885=0A> (2 rows)=0A>=0A> At this point=
 you would think you have two equal values.=0A=0AAt this point, try this:=
=0A=0Aselect * from test where value =3D '10.3885';=0A=0A=A0id |=A0 value =
=0A----+---------=0A=A0 2 | 10.3885=0A(1 row)=0A=0ANow try this:=0A=0Aselec=
t * from test where value =3D 10.3885;=0A=A0id | value=0A----+-------=0A(0 =
rows)=0A=0AAlways remember that floating point types are *approximate* data=
=0Atypes; equality often does not behave as you might expect.=A0 You're=0Ap=
robably aware of the below issues, but just in case:=0A=0Aselect '99999999'=
::real =3D ('99999999'::real + '1'::real);=0A=A0?column? =0A----------=0A=
=A0t=0A(1 row)=0A=0Aselect '.1'::real::float;=0A=A0=A0=A0=A0=A0 float8=A0=
=A0=A0=A0=A0 =A0=0A-------------------=0A=A00.100000001490116=0A(1 row)=0A=
=0A--=0AKevin Grittner=0AEnterpriseDB: http://www.enterprisedb.com=0AThe En=
terprise PostgreSQL Company