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: (view raw, whole thread or download thread mbox)
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];


   printf("           Stored double number: %25.18g\n",v);
   printf("            Converted to string: %s\n",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
Tel./Fax:  (+351) 289 800950 / 289 819403

In response to


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-2017 The PostgreSQL Global Development Group