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