Re: float output precision questions - Mailing list pgsql-hackers

Stephan Szabo wrote:
> On Wed, 30 Oct 2002, Pedro Miguel Frazao Fernandes Ferreira wrote:
>>Stephan Szabo wrote:
>>>On Tue, 29 Oct 2002, Peter Eisentraut wrote:
>>>>Pedro Miguel Frazao Fernandes Ferreira writes:
>>>>>Is there a way to set query output precision to maximum precision ?
>>>>>For the type of application I mentioned this is crucial. People want to
>>>>>get the 'same' numbers, from querys or dumps, as they inserted them.
>>>>
>>>How do you define maximum precision and "same"?  With simple test programs
>>>in C, using two digits more than DBL_DIG for printf specifier, it's easy
>>>for me to find numbers that "change" decimal string representation in the
>>>decimal representation -> double -> decimal representation conversion(*).
>>>The final double you get from the second conversion should be the same as
>>>the first, but is that what you need or do you need a stronger guarantee
>>>than that?
>>
>>When I say "same" I am talking about having a number 'stored' in double
>>format in some client, inserting it in PostgreSQL float8 field and get
>>it to the client as it was before:
>>
>>   [Some client]  (insert)   [PostgreSQL]  (query)  [Some client]
>>(double number a)-------->(float8 number)------->(double number b)
>>
>>"same" is so that a==b is true.
>>With current float8 output this is not allways true.
>>
>>I believe this should allways be true for numbers which are originally
>>stored in double format.
> 
> The problem is that there are two competing needs here.  One is the above,
> the other other is that you get something that has the same decimal
> representation (within the float's ability to store the number). Right now
> the system does the latter since for most people, that seems to be the
> guarantee they want.

Look at this example:

1.79769313486231571e+308 is the largest floating point number 
representable by a C double in x86.

In C this is possible:

#include <stdio.h>
#include <stdlib.h>

int main(void)
{  double v;  char a[30];
  v=1.79769313486231571e+308;
  printf("           Stored double number: %25.18g\n",v);  sprintf(a,"%25.18g",v);  printf("            Converted to
string:%s\n",a);  v=atof(a);  printf("Converted from string to double: %25.18g\n",v);
 
}

Using standard PostgreSQL query output it would not be possible to get 
this number, which has representation as a double.

I fetched the PostgreSQL source from Debian, changed 
src/backend/utils/adt/float.c to do sprintf(ascii, "%25.18g", num) 
instead of sprintf(ascii, "%.*g", DBL_DIG, num), compiled and installed. 
Now I can get the number as is.

I understand that if people insert a value of 1.1 in a double, they want 
to get 1.1 without knowing that in fact the stored number is 
1.10000000000000009. But do you understand that if some people insert, 
for example, a value of 1.79769313486231571e+308 they dont want to get 
1.79769313486232e+308 which does not compare equal (in Matlab or C) to 
the first ? This is a bug.

> This would probably make sense as an option, so why don't you look at the
> past discussions and see if you can come up with a solution that keeps
> everyone happy (and preferably implement it, but...) :)

but ???

I have a sugestion:

To have parameters, say DOUBLE_FORMAT and FLOAT_FORMAT, which could have  option values of 'SHORT' and 'LONG'.
Option 'SHORT' would be default and produce the standard sprintf(ascii,...
Option 'LONG' would produce sprintf(ascii, "%25.18g", num).

Other way would be to have number parameters to be used in the sprintf 
calls, in place of 25 and 18, in the format string.

> 
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
> 
> 


-- 
----------------------------------------------------------------------
Pedro Miguel Frazao Fernandes Ferreira
Universidade do Algarve
Faculdade de Ciencias e Tecnologia
Campus de Gambelas
8000-117 Faro
Portugal
Tel./Fax:  (+351) 289 800950 / 289 819403
http://w3.ualg.pt/~pfrazao