Re: Floating point error

Tom Duffey wrote:
> Here is a smaller test case that does not involve Java. I guess this probably 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.
>
> Create a test table, insert a couple values and view the results:
>
> 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)

SET extra_float_digits=3;
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 type:
>
> ALTER TABLE test ALTER COLUMN value TYPE DOUBLE PRECISION;
> SELECT * FROM test;
>
> id | value
> ----+------------------
> 1 | 10.3884572982788
> 2 | 10.388500213623
> (2 rows)

Yours,
Laurenz Albe