Обсуждение: 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.
Re: ResultSet getString() result differs in 8.3 jdbc (compared to 8.1), with "real" type
От
Guillaume Cottenceau
Дата:
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@postgresql.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@postgresql.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.