Re: [GENERAL] Floating point error

This conversation has moved beyond my ability to be useful but I want to remind everyone of my original issues in case it helps you improve the docs:

1) Data shown in psql did not match data retrieved by JDBC. I had to debug pretty deep into the JDBC code to confirm that a value I was staring at in psql was different in JDBC. Pretty weird, but I figured it had something to do with floating point malarky.

2) The problem in #1 could not be reproduced when running on our test database. Again very weird, because as far as psql was showing me the values in the two databases were identical. I used COPY to transfer some data from the production database to the test database.

I now know that what you see in psql is not necessarily what you see in JDBC. I also know that you need to set extra_float_digits = 3 before using COPY to transfer data from one database to another or risk differences in floating point values. Sounds like both pg_dump and the JDBC driver must be doing this or its equivalent on their own.

If the numeric types page of the documentation had mentioned the extra_float_digits then I might have been able to solve my own problem. I'd like you to add some mention of it even if it is just handwaving but will let you guys hash it out from here. Either way, PostgreSQL rocks!

Tom

On Mar 5, 2013, at 12:38 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Maciek Sakrejda <m(dot)sakrejda(at)gmail(dot)com> writes:
>> On Tue, Mar 5, 2013 at 10:23 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> Basically, the default behavior is tuned to the expectations of people
>>> who think that what they put in is what they should get back, ie we
>>> don't want the system doing this by default:
>>>
>>> regression=# set extra_float_digits = 3;
>>> SET
>>> regression=# select 0.1::float4;
>>> float4
>>> -------------
>>> 0.100000001
>>> (1 row)
>>>
>>> regression=# select 0.1::float8;
>>> float8
>>> ---------------------
>>> 0.10000000000000001
>>> (1 row)
>>>
>>> We would get a whole lot more bug reports, not fewer, if that were
>>> the default behavior.
>
>> Isn't this a client rendering issue, rather than an on-the-wire encoding issue?
>
> Nope, at least not unless you ask for binary output format (which
> introduces a whole different set of portability gotchas, so it's
> not the default either).
>
> regards, tom lane

--
Tom Duffey
tduffey(at)trillitech(dot)com
414-751-0600 x102