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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Re: float8 auto truncation issue in ODBC v. PGSQL

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

Re: float8 auto truncation issue in ODBC v. PGSQL

This is a multi-part message in MIME format.
--------------000909070806060603080607
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

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

--------------000909070806060603080607
Content-Type: text/x-vcard; charset=utf-8;
name="greg.campbell.vcf"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
filename="greg.campbell.vcf"

begin:vcard
fn:Greg Campbell
n:Campbell;Greg
org:Michelin North America - US5 Lexington;ENG-ASE
email;internet:greg.campbell@us.michelin.com
title:ASE Systems Engineer
tel;work:803-951-5561/x75561
x-mozilla-html:FALSE
version:2.1
end:vcard


--------------000909070806060603080607
Content-Type: text/plain
Content-Disposition: inline
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

--------------000909070806060603080607--

Re: float8 auto truncation issue in ODBC v. PGSQL

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Re: float8 auto truncation issue in ODBC v. PGSQL

"Campbell, Greg" 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

Re: float8 auto truncation issue in ODBC v. PGSQL

This is a multi-part message in MIME format.
--------------030706070301070805000103
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

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" 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

--------------030706070301070805000103
Content-Type: text/x-vcard; charset=utf-8;
name="greg.campbell.vcf"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
filename="greg.campbell.vcf"

begin:vcard
fn:Greg Campbell
n:Campbell;Greg
org:Michelin North America - US5 Lexington;ENG-ASE
email;internet:greg.campbell@us.michelin.com
title:ASE Systems Engineer
tel;work:803-951-5561/x75561
x-mozilla-html:FALSE
version:2.1
end:vcard


--------------030706070301070805000103
Content-Type: text/plain
Content-Disposition: inline
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

--------------030706070301070805000103--

Re: float8 auto truncation issue in ODBC v. PGSQL

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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match