Search Postgresql Archives

Re: Floating point error

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxx> 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
tduffey@xxxxxxxxxxxxxx
414-751-0600 x102



-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux