Обсуждение: float8 auto truncation issue in ODBC v. PGSQL
Not sure if the ODBC list is the best place for this question, but I've encountered some strange behavior and was wondering if anyone had an idea as to what's going on. I've got a float8 data type in a column. I have a value '1.0475' inserted into it. That number cannot of course be perfectly represented as a float8. When I perform a test like this in MSSQL 7, e.g., and select it using any variety of tools I get this out: 1.0475000000000001 When I use my own ODBC SQL client to select the same value from my Postgres 8.0 database, I get the same thing: 1.0475000000000001 *However*, and here's the troubling part, when I select the exact same value using psql (and I have reason to believe the same thing is going on inside pl/pgsql functions), the value that comes out is: 1.0475 Now, some of you may say, "What's your problem? That's actually the correct value!" That's true, it is. However, I'm confused as to the differences between the result I get *from* PG via my ODBC client and what Postgres shows with it's internal tools. The reason this came up is, for some uninteresting reason, I really need to see the former value (w/ the trailing 1) inside Postgres (a pl/pgsql function) when making a computation. I'd appreciate any insight, and apologize if I missed something obvious in the docs or via my googling. John
postgresql.org@tgice.com writes:
> When I use my own ODBC SQL client to select the same value from my
> Postgres 8.0 database, I get the same thing:
> 1.0475000000000001
> *However*, and here's the troubling part, when I select the exact same
> value using psql (and I have reason to believe the same thing is going
> on inside pl/pgsql functions), the value that comes out is:
> 1.0475
By default, the float8 output routine prints a digit or so less than it
could, precisely to avoid that sort of unsightly result. You can fool
with float_extra_digits (I think that's the right name, check the docs)
if you need to adjust this behavior.
This would only matter within a plpgsql function if you are doing
something that forces the binary value to be converted to text. Which,
if you're doing tense float computation, you probably don't want to have
happen at all.
regards, tom lane
I've disassociated floats and exactness, that is floating point representations and exact matches do not seem to go together. The idea was made more profound when I started looking into the multitude of options in representing a float in 16, 32 or 64 bits. There are so many different ways to allocate bits for the number, and bits for the exponent, leading to radically different precisions. Between a value on the server and a value on the client a difference out in the 15th decimal place hardly seems surprising. That float_extra_digits is a cool thing to know. Tom Lane wrote: > postgresql.org@tgice.com writes: > >>When I use my own ODBC SQL client to select the same value from my >>Postgres 8.0 database, I get the same thing: >> 1.0475000000000001 > > >>*However*, and here's the troubling part, when I select the exact same >>value using psql (and I have reason to believe the same thing is going >>on inside pl/pgsql functions), the value that comes out is: >> 1.0475 > > > By default, the float8 output routine prints a digit or so less than it > could, precisely to avoid that sort of unsightly result. You can fool > with float_extra_digits (I think that's the right name, check the docs) > if you need to adjust this behavior. > > This would only matter within a plpgsql function if you are doing > something that forces the binary value to be converted to text. Which, > if you're doing tense float computation, you probably don't want to have > happen at all. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster
Вложения
postgresql.org@tgice.com wrote: > Not sure if the ODBC list is the best place for this question, but I've > encountered some strange behavior and was wondering if anyone had an > idea as to what's going on. > > I've got a float8 data type in a column. I have a value '1.0475' > inserted into it. That number cannot of course be perfectly represented > as a float8. > > When I perform a test like this in MSSQL 7, e.g., and select it using > any variety of tools I get this out: > > 1.0475000000000001 > > When I use my own ODBC SQL client to select the same value from my > Postgres 8.0 database, I get the same thing: > > 1.0475000000000001 > > *However*, and here's the troubling part, when I select the exact same > value using psql (and I have reason to believe the same thing is going > on inside pl/pgsql functions), the value that comes out is: > > 1.0475 > > Now, some of you may say, "What's your problem? That's actually the > correct value!" That's true, it is. However, I'm confused as to the > differences between the result I get *from* PG via my ODBC client and > what Postgres shows with it's internal tools. The odbc driver automatically issues *set extra_float_digits to 2* command at the beginning. regards, Hiroshi Inoue
"Campbell, Greg" <greg.campbell@us.michelin.com> writes: > I've disassociated floats and exactness, that is floating point > representations and exact matches do not seem to go together. The issue is that "float" types actually means fractions encoded in base 2 for efficiency reasons. Almost every time you go back and forth between base 2 and base 10 you have to round, there is no exact mapping between those two spaces. For instance you can not write 1/3 (one third) in base 10 whereas you can in base 3 using just a couple of digits (it's just "0.1") > The idea was made more profound when I started looking into the > multitude of options in representing a float in 16, 32 or 64 > bits. There are so many different ways to allocate bits for the > number, and bits for the exponent, leading to radically different > precisions. Actually on today's hardware I thought it was hard to find anything else than IEEE754 32 and 64 bits floats, standardized across all platforms, and 32 bits values being a subset of 64 bits. So that does not look like "many different ways" to me. Could you detail? > Between a value > on the server and a value on the client a difference out in the 15th > decimal place hardly seems surprising. Whether conversions and roundings happen on the server on or the client does not seem to change the problem much IMHO.
My comments were general more than specific to our situation. I work with some legacy systems that are quite old (80'x unix, OpenVMS, IBM mainframes). I have built MessageQ-ing application and discover the structures of floating points were not reliably simple transfers. The original programs (in Fortran and Pascal) may have using specification called F-floating point, and G-floating point. There might be correlation to IEEE 754 OR IEEE 854, depending on size (16,32,64 bit). So as a policy, I use comparison of Abs(x-y) < 0.0001 instead of expecting exact 1.475 and being surprised to fine 1.47500000001. I realize it might not apply as I pull PostgreSQL from a Linux box into say a VB6 application. Marc Herbert wrote: > "Campbell, Greg" <greg.campbell@us.michelin.com> writes: > > >>I've disassociated floats and exactness, that is floating point >>representations and exact matches do not seem to go together. > > > The issue is that "float" types actually means fractions encoded in > base 2 for efficiency reasons. Almost every time you go back and forth > between base 2 and base 10 you have to round, there is no exact > mapping between those two spaces. > > For instance you can not write 1/3 (one third) in base 10 whereas you > can in base 3 using just a couple of digits (it's just "0.1") > > > >>The idea was made more profound when I started looking into the >>multitude of options in representing a float in 16, 32 or 64 >>bits. There are so many different ways to allocate bits for the >>number, and bits for the exponent, leading to radically different >>precisions. > > > Actually on today's hardware I thought it was hard to find anything > else than IEEE754 32 and 64 bits floats, standardized across all > platforms, and 32 bits values being a subset of 64 bits. So that does > not look like "many different ways" to me. Could you detail? > > > >>Between a value >>on the server and a value on the client a difference out in the 15th >>decimal place hardly seems surprising. > > > Whether conversions and roundings happen on the server on or the > client does not seem to change the problem much IMHO. > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly
Вложения
Tom Lane wrote: > By default, the float8 output routine prints a digit or so less than it > could, precisely to avoid that sort of unsightly result. You can fool > with float_extra_digits (I think that's the right name, check the docs) > if you need to adjust this behavior. Thanks Tom, You rock. The option is extra_float_digits. I figured Postgres would have something handy like this available. I just set that to "2" in my postgresql.conf (the maximum allowed value), and the psql queries return what I was trying to get. > This would only matter within a plpgsql function if you are doing > something that forces the binary value to be converted to text. Which, > if you're doing tense float computation, you probably don't want to have > happen at all. Yes, I'll have to look into this a bit more as the correct value still doesn't seem to be coming out of my function. John