Re: Floating point error

Tom Duffey wrote:
> Here is a smaller test case that does not involve Java. I guess this prob=
ably is just due to floating
> point error when the initial value is inserted that is too large for the =
field but it's still a
> surprise.
>=20
> Create a test table, insert a couple values and view the results:
>=20
> CREATE TABLE test (
>     id INTEGER PRIMARY KEY,
>     value REAL NOT NULL
> );
>=20
> INSERT INTO test (id, value) VALUES (1, 10.3884573), (2, 10.3885);
> SELECT * FROM test;
>=20
>  id |  value
>  ----+---------
>    1 | 10.3885
>    2 | 10.3885
> (2 rows)

SET extra_float_digits=3D3;
SELECT * FROM test;

 id |   value
----+------------
  1 | 10.3884573
  2 | 10.3885002
(2 rows)

PostgreSQL by default omits the last three digits to avoid
differences on different architectures (I think).

When you convert to double precision, you'll see these digits.

> At this point you would think you have two equal values. Now change the t=
ype:
>=20
> ALTER TABLE test ALTER COLUMN value TYPE DOUBLE PRECISION;
> SELECT * FROM test;
>=20
>  id |      value
> ----+------------------
>   1 | 10.3884572982788
>   2 |  10.388500213623
> (2 rows)

Yours,
Laurenz Albe