Re: numeric precision when raising one numeric to another.

On Thu, May 19, 2005 at 10:41:51AM -0700, Dann Corbit wrote:
> We use Moshier's excellent qfloat numbers.
> http://www.moshier.net/qlib.zip
> Documentation:
> http://www.moshier.net/qlibdoc.html
>
> So, if you do the following query using CONNX:
> select convert(pow(9.5,5.9), varchar)
> You will get:
> 586906.97548405202106027547827738573075504470845684721318303336760202394
> 5916438064873363100477233500417619

But it's not accurate enough with the default settings. For example
2^100:

# select exp( ln(2::numeric) * 100 );
exp
--------------------------------------------------
1267650600228229400579922894637.9015824515440063
(1 row)

The answer should be:
1267650600228229401496703205376

So it's wrong from the 14th digit onwards. If that's the case you may
as well stick to using floating point. It does however appear you can
influence the precision, See:

# select exp( ln(2::numeric(50,30)) * 100 );
exp
----------------------------------------------------------------
1267650600228229401496703205375.991370405139384131115870698781
(1 row)

Using numeric(50,25) gets you only 28 correct digits. So, if you know
how big your result is going to be you can adjust the types to match
and get whatever precision you want. Given that you can estimate the
number of digits easily enough (it's linear with the value before the
exp()) maybe you can get it to automatically choose the right
precision?
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.