ResultSet getString() result differs in 8.3 jdbc (compared to 8.1), with "real" type

Hi,

I made some searches also, however found nothing exactly related to this
problem. We just shifted from using 8.1 driver to 8.3 however seems that 8.3
driver handles differently Postgresql floating type values, when using
org.postgresql.jdbc2.AbstractJdbc2ResultSet.java getString() on them ?
For example, when there is "real" type value 20.7 in database and we try to
get it through getString() then old 8.1 driver returns nicely 20.7, but new
8.3 driver is returning something like 20.700001.

As "real" is floating type value then presumably this long 20.700001 can
indeed be how database is holding value 20.7 ? However when I was comparing
8.1 and 8.3 driver sources then no changes that could have caused it caught
my eye...
So my question would be that if such getString() behaviour is desired (?)
(it is also in 8.4 driver), then can someone please give an hint where
exactly can this behaviour be manipulated/turned off/etc... ?

(NB! yes, the correct way would be to use getFloat() ofcourse)

Regards,
Tanel.
--
View this message in context: http://www.nabble.com/ResultSet-getString%28%29-result-differs-in-8.3-jdbc-%28compared-to-8.1%29%2C-with-%22real%22-type-tp23536933p23536933.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

Tanel <tanel.ehrenpreis 'at' finestmedia.ee> writes:

> Hi,
>
> I made some searches also, however found nothing exactly related to this
> problem. We just shifted from using 8.1 driver to 8.3 however seems that 8.3
> driver handles differently Postgresql floating type values, when using
> org.postgresql.jdbc2.AbstractJdbc2ResultSet.java getString() on them ?
> For example, when there is "real" type value 20.7 in database and we try to
> get it through getString() then old 8.1 driver returns nicely 20.7, but new
> 8.3 driver is returning something like 20.700001.

Wanting to manipulate precise decimal values with float or double
datatypes is bound to fail, because the binary representation of
values make it impossible - it holds the mantissa and the
exponent, so some rounding must happen. The "display" will depend
on rounding and so forth. 20.7 doesn't exist "as such" in the
computer, when using float or double datatypes.

If you want to manipulate precise decimal numbers, use the SQL
NUMERIC data type (BigDecimal in java); or else, you can always
use "fixed point" numbers, e.g. if you need a monetary value you
may store cent values as an integer (2070 in database to
represent $20.7) and shift by 100 only for user interfacing.

Beside, getString() on a floating type value is just wrong, but
you guessed it already! :)

--
Guillaume Cottenceau

On Thu, 14 May 2009, Tanel wrote:

> I made some searches also, however found nothing exactly related to this
> problem. We just shifted from using 8.1 driver to 8.3 however seems that 8.3
> driver handles differently Postgresql floating type values, when using
> org.postgresql.jdbc2.AbstractJdbc2ResultSet.java getString() on them ?
> For example, when there is "real" type value 20.7 in database and we try to
> get it through getString() then old 8.1 driver returns nicely 20.7, but new
> 8.3 driver is returning something like 20.700001.
>
> So my question would be that if such getString() behaviour is desired (?)
> (it is also in 8.4 driver), then can someone please give an hint where
> exactly can this behaviour be manipulated/turned off/etc... ?
>

The setting that changed is extra_float_digits. The newer driver is
setting it to two rather than the default zero to restore float values as
accurately as possible. You can override this by issuing your own SET
command upon connection.

http://www.postgresql.org/docs/8.3/static/runtime-config-client.html#RUNTIME-CONFIG-CLIENT-FORMAT

Kris Jurka

Guillaume Cottenceau-2 wrote:
>
> Tanel <tanel.ehrenpreis 'at' finestmedia.ee> writes:
>
>> Hi,
>>
>> I made some searches also, however found nothing exactly related to this
>> problem. We just shifted from using 8.1 driver to 8.3 however seems that
>> 8.3
>> driver handles differently Postgresql floating type values, when using
>> org.postgresql.jdbc2.AbstractJdbc2ResultSet.java getString() on them ?
>> For example, when there is "real" type value 20.7 in database and we try
>> to
>> get it through getString() then old 8.1 driver returns nicely 20.7, but
>> new
>> 8.3 driver is returning something like 20.700001.
>
> Wanting to manipulate precise decimal values with float or double
> datatypes is bound to fail, because the binary representation of
> values make it impossible - it holds the mantissa and the
> exponent, so some rounding must happen. The "display" will depend
> on rounding and so forth. 20.7 doesn't exist "as such" in the
> computer, when using float or double datatypes.
>
> If you want to manipulate precise decimal numbers, use the SQL
> NUMERIC data type (BigDecimal in java); or else, you can always
> use "fixed point" numbers, e.g. if you need a monetary value you
> may store cent values as an integer (2070 in database to
> represent $20.7) and shift by 100 only for user interfacing.
>
> Beside, getString() on a floating type value is just wrong, but
> you guessed it already! :)
>
> --
> Guillaume Cottenceau
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc
>
>

Hi,

Yes, it is sometimes quite frustrating when all different kind of data types
are used as Strings. Unfortunately this is quite big enterprise portal that
is running with slightly different versions in 7 countries with ancient
system core that was made some other developers ages ago :) . So we are
trying to cause as little changes at the moment as possible, as testing all
functionality would take a week :) . However I certainly strongly suggest
client to take into plan changing this core system part.

We are currently using floating types for some additional description values
only, where it is not important if few digits in the end are not exact
(numbers themselves are quite small also), so numeric types using would
probably add considerable overload.... currently the problem was just that
they were quite "ugly" when showed in the portal (etc 27.00001 when using
getString()), no real functionality loss...
As the old 8.1 driver´s getString() did not use 2 extra digits, then at the
moment we were just looking for a quick fix, system could be re-structured
later, but thanx ;)

Tanel.
--
View this message in context: http://www.nabble.com/ResultSet-getString%28%29-result-differs-in-8.3-jdbc-%28compared-to-8.1%29%2C-with-%22real%22-type-tp23536933p23554573.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

Kris Jurka wrote:
>
>
>
> On Thu, 14 May 2009, Tanel wrote:
>
>> I made some searches also, however found nothing exactly related to this
>> problem. We just shifted from using 8.1 driver to 8.3 however seems that
>> 8.3
>> driver handles differently Postgresql floating type values, when using
>> org.postgresql.jdbc2.AbstractJdbc2ResultSet.java getString() on them ?
>> For example, when there is "real" type value 20.7 in database and we try
>> to
>> get it through getString() then old 8.1 driver returns nicely 20.7, but
>> new
>> 8.3 driver is returning something like 20.700001.
>>
>> So my question would be that if such getString() behaviour is desired (?)
>> (it is also in 8.4 driver), then can someone please give an hint where
>> exactly can this behaviour be manipulated/turned off/etc... ?
>>
>
> The setting that changed is extra_float_digits. The newer driver is
> setting it to two rather than the default zero to restore float values as
> accurately as possible. You can override this by issuing your own SET
> command upon connection.
>
> http://www.postgresql.org/docs/8.3/static/runtime-config-client.html#RUNTIME-CONFIG-CLIENT-FORMAT
>
> Kris Jurka
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc
>
>

Hi,

Thank you, that did it :)
Actually when I debugged and compared the source codes of 8.1 and 8.3 I
noticed and experimented with that value also, but as there are v2 and v3
ConnectionFactoryImpl.java, then somehow I missed the latter one, probably
the day was already too long by that time :)

Tanel.
--
View this message in context: http://www.nabble.com/ResultSet-getString%28%29-result-differs-in-8.3-jdbc-%28compared-to-8.1%29%2C-with-%22real%22-type-tp23536933p23554615.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.