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