Re: WIP: to_char, support for EEEE format

From: Brendan Jurd <direvus(at)gmail(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Euler Taveira de Oliveira <euler(at)timbira(dot)com>, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>, Robert Haas <robertmhaas(at)gmail(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: WIP: to_char, support for EEEE format
Date: 2009-08-04 02:09:50
Message-ID: 37ed240d0908031909w2ee5e838jddb83b471d54246b@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

2009/8/3 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Euler Taveira de Oliveira <euler(at)timbira(dot)com> writes:
>> As I said in a prior e-mail, Oracle has a diferent overflow limit (-84 to 127).
>> In PostgreSQL, the numeric datatype can have up to 1000 digits (ie 1e+999) and
>> the double precision datatype can have up to 309 digits (ie 1e-307 or 1e+308).
>> We should support up to 3 exponent digits so all of our native datatypes are
>> covered by the to_char() function.
>
> Uh, no, we had better support more.  The actual limit of the current
> numeric format is 1e+131072.
>
> As long as we consider that EEEE should emit as many exponent digits
> as needed, this isn't particularly critical.  But it would be if we
> try to specify an exact number of output digits.
>

Well, I tried this and as it turns out the patch casts the value to a
float8 in order to pass it on to snprintf for sci-notation formatting.
See the following chunk in numeric_to_char():

else if (IS_EEEE(&Num))
{
float8 val;

val = DatumGetFloat8(DirectFunctionCall1(numeric_float8,
NumericGetDatum(value)));

numstr = orgnum = (char *) palloc(MAXDOUBLEWIDTH + 1);
len = snprintf(orgnum, MAXDOUBLEWIDTH + 1, "%.*e", Num.post, val);
}

This leads to the following behaviour:

=# select to_char(1e+1000::numeric, '9.99EEEE');
ERROR: "10000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000"
is out of range for type double precision

If we want to support the full range of numeric values with EEEE, I
guess we would need to write our own implementation of scientific
notation rather than depend on sprintf()'s?

Cheers,
BJ

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2009-08-04 02:18:51 Re: async notification patch for dblink
Previous Message KaiGai Kohei 2009-08-04 02:04:07 Re: SE-PostgreSQL Specifications