Stephan Szabo wrote:
Look at this example: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.
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.
but ???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...) :)
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 ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org
