If you want to create a pow() function for numeric using existing
numeric functions, it [the new function] should be aware of the
precision of the inputs, and the precision of the output should be their
product.
So, if you do pow(numeric(10,5), numeric(10,5)) then the result column
should be numeric(100,25) if you want to retain full precision.
> -----Original Message-----
> From: Martijn van Oosterhout [mailto:kleptog@svana.org]
> Sent: Thursday, May 19, 2005 2:02 PM
> To: Dann Corbit
> Cc: Alvaro Herrera; John Burger; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] 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@svana.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.