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(dot)albe(at)wien(dot)gv(dot)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
tduffey(at)trillitech(dot)com
414-751-0600 x102