Re: Floating point error

That's exactly what I was looking for. We use COPY to transfer data from =
a 1 billion+ row table to a test database and were confused why the =
results looked the same but were obviously not. Sounds like we need to =
use the extra_float_digits setting to include all the available =
information when transferring the data.

Thanks for the explanation.

Tom

On Feb 25, 2013, at 8:00 AM, Albe Laurenz <laurenz.albe@wien.gv.at> =
wrote:

> 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.
>>=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)
>=20
> SET extra_float_digits=3D3;
> SELECT * FROM test;
>=20
> id |   value
> ----+------------
>  1 | 10.3884573
>  2 | 10.3885002
> (2 rows)
>=20
> PostgreSQL by default omits the last three digits to avoid
> differences on different architectures (I think).
>=20
> When you convert to double precision, you'll see these digits.
>=20
>> At this point you would think you have two equal values. Now change =
the type:
>>=20
>> ALTER TABLE test ALTER COLUMN value TYPE DOUBLE PRECISION;
>> SELECT * FROM test;
>>=20
>> id |      value
>> ----+------------------
>>  1 | 10.3884572982788
>>  2 |  10.388500213623
>> (2 rows)
>=20
> Yours,
> Laurenz Albe

--
Tom Duffey
tduffey@trillitech.com
414-751-0600 x102