Re: Floating point error

Hi Everyone,

To bring closure to this thread, my whole problem was caused by not knowing about the extra_float_digits setting. We have a script that uses COPY to transfer a subset of rows from a very large production table to a test table. The script was not setting extra_float_digits so the values did not match even though they appeared to match when running queries in psql. Definitely another gotcha for floating point values and it might be a good idea to mention this setting on the "Numeric Types" page of the docs.

Thanks to all who chimed in to help!

Tom

On Feb 28, 2013, at 7:05 PM, James Cloos <cloos(at)jhcloos(dot)com> wrote:

>>>>>> "TD" == Tom Duffey <tduffey(at)trillitech(dot)com> writes:
>
> TD> Riddle me this. I have a database column of type "real" that gets
> TD> mapped to a Java field of type double via JDBC. ...
>
> TD> - Selecting values from both test and production DBs using psql
> TD> shows "10.3885" as the value
>
> TD> - The Java app on production shows "10.3884573" while the test app
> TD> shows "10.3885"
>
> I suspect the issue is that psql(1) and whatever java method you use to
> convert the floats to text choose different rounding.
>
> By default, it seems that psql(1) uses something like printf("%.4f",...)
> whereas your java app calls a routing which works more like "%.7f".
>
> (The wire format for floats is the same as they are stored, not a text
> representation thereof.)
>
> -JimC
> --
> James Cloos <cloos(at)jhcloos(dot)com> OpenPGP: 1024D/ED7DAEA6

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