Re: Floating point error - Mailing list pgsql-general

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
); =20

INSERT INTO test (id, value) VALUES (1, 10.3884573), (2, 10.3885);
SELECT * FROM test;

 id |  value =20
 ----+---------
   1 | 10.3885
   2 | 10.3885
(2 rows)

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      =20
----+------------------
  1 | 10.3884572982788
  2 |  10.388500213623
(2 rows)

Values no longer equal and the first one is in fact closer to what as =
originally inserted. Why is this? Is this simply caused by how the =
initially inserted value is stored as floating point?

If you create a "copy" of the database before changing the field type =
then both values get inserted as "10.3885." Changing the type then =
results in two equal values. Maybe this is just another pitfall of using =
floating point numbers and at this point I am just trying to identify =
exactly where our errors are being introduced so can anyone confirm the =
above behavior is correct? In our real world example we are not changing =
the type but are instead getting the second value w/id =3D 1 above when =
using JDBC to retrieve values into a Java double field.

I ran the above on PostgreSQL 9.1.2 and 9.2.2 with the same results.

Tom

On Feb 24, 2013, at 9:17 PM, Adrian Klaver <adrian.klaver@gmail.com> =
wrote:

> On 02/24/2013 06:58 PM, Tom Duffey wrote:
>>=20
>> On Feb 24, 2013, at 8:44 PM, Adrian Klaver <adrian.klaver@gmail.com> =
wrote:
>>=20
>>> On 02/24/2013 06:13 PM, Tom Duffey wrote:
>>>> Hi Everyone,
>>>>=20
>>>> Riddle me this. I have a database column of type "real" that gets =
mapped to a Java field of type double via JDBC. We have two databases, =
test and production, and the test database is periodically blown away =
and reloaded from a copy of production. We recently noticed that some =
values do not match when viewed within our application on test vs. =
production. More specifically:
>>>>=20
>>>> - Selecting values from both test and production DBs using psql =
shows "10.3885" as the value
>>>> - The Java app on production shows "10.3884573" while the test app =
shows "10.3885"
>>>>=20
>>>> I have a hunch that when the value was originally inserted into the =
production DB it probably contained more than the 6 digits supported by =
the real data type. It may have even been exactly the "10.3884573" value =
we see when retrieving via JDBC on production. What I don't understand =
is why when the value gets mapped back to Java via JDBC those extra =
digits are coming back. Can anyone explain this or do you think I'm on =
the wrong track? I stepped through code and it sure seems like the extra =
information is coming back from the JDBC driver.
>>>=20
>>> Are the production and test apps running on the same platform i.e. =
OS, bitness, etc.
>>=20
>> Yes, the production and test apps are running on the same platform. =
The Java apps themselves are physically on the same Linux server. The =
production and test databases reside within the same instance of =
PostgreSQL.
>>=20
>> Also, I should have mentioned up front that I am well aware of the =
pitfalls of using floating point values and also the fact that =
PostgreSQL's "real" data type supports 6 digits of precision. What I do =
not understand is why my JDBC driver is returning more information than =
what I receive in psql or if I operate on a copy of the database. This =
leads me to believe that more information was available at insertion =
time and is somehow being made available to my application even though =
the data type should only store 6 digits. Let me see if I can write a =
quick little test case.
>>=20
>=20
> Well I guess you could look in the dump file and see what is recorded =
there.
>=20
>> Tom
>>=20
>=20
>=20
> --=20
> Adrian Klaver
> adrian.klaver@gmail.com

--
Tom Duffey
tduffey@trillitech.com
414-751-0600 x102