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.a...@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. >> >> 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 -- Tom Duffey tduf...@trillitech.com 414-751-0600 x102 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
