Skip site navigation (1) Skip section navigation (2)

Re: float output precision questions

From: Pedro Miguel Frazao Fernandes Ferreira <pfrazao(at)ualg(dot)pt>
To: Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com>
Cc: Peter Eisentraut <peter_e(at)gmx(dot)net>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: float output precision questions
Date: 2002-10-30 17:01:19
Message-ID: 3DC0105F.6040108@ualg.pt (view raw or flat)
Thread:
Lists: 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


In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2002-10-30 17:42:25
Subject: Re: Trigger on 'create table' ?
Previous:From: Paul RamseyDate: 2002-10-30 16:24:56
Subject: Re: OO future

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group