Re: numeric precision when raising one numeric to another.

From: Martijn van Oosterhout <kleptog(at)svana(dot)org>
To: "Jim C(dot) Nasby" <decibel(at)decibel(dot)org>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Scott Marlowe <smarlowe(at)g2switchworks(dot)com>, pgsql-general(at)postgresql(dot)org
Subject: Re: numeric precision when raising one numeric to another.
Date: 2005-05-21 11:43:26
Message-ID: 20050521114326.GA13400@svana.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-patches

On Fri, May 20, 2005 at 12:22:33PM -0500, Jim C. Nasby wrote:
> > which you could take as requiring us to provide numeric equivalents of
> > every floating-point operator, but I don't find that argument very
> > convincing for operations that are inherently not going to give exact
> > results. The spec demands exact results from addition, subtraction,
> > and multiplication, but as soon as you get to division they punt; let
> > alone transcendental functions.
>
> ISTM what's more important than be exact is respecting precision. If I'm
> remembering this correctly from high school, multiplying two numbers
> each having 10 significant digits means you then have 20 significant
> digits, so we should at least respect that. Which means
> numeric(500)^numeric(500) should give an exact numeric(1000), which I
> don't think is a given when casting to a double.

Wrong.

numeric(500) * numeric(500) = numeric(1000)
numeric(500) ^ numeric(500) = numeric(10 ^ 503) >> googleplex

You do not have enough memory to store the exact result. There are not
enough atoms in the universe to store this result. That's one reason
why you can't guarentee an exact result. Even numeric(20) ^ numeric(20)
= numeric( 10 ^ 22 )

> I'm not sure how this changes if you're using a fractional exponent. But
> it seems like a pretty serious issue if you're doing financial
> calculations and those are sometimes done in floating point under the
> covers.

Financial calculations are a red herring. They don't deal with less
than hundredths of a cent or more than trillions of dollars so 20
significant digits is easily enough. I would say to place an upper
limit at say 100 digits. It you want better, go get a real math
package.

Have a nice day,
--
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.

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2005-05-21 14:30:47 Re: Exception Handling in C-Language Functions?
Previous Message Felix E. Klee 2005-05-21 11:21:14 Re: Exception Handling in C-Language Functions?

Browse pgsql-patches by date

  From Date Subject
Next Message Neil Conway 2005-05-21 12:16:01 Re: contrib/ gist cleanup
Previous Message Christopher Kings-Lynne 2005-05-21 04:39:59 Re: patches for items from TODO list