Re: maximum digits for NUMERIC

On Fri, Mar 25, 2011 at 06:09:54PM +0000, Gianni Ciolli wrote:
> On Fri, Mar 25, 2011 at 08:46:17AM +0000, Gianni Ciolli wrote:
> > On Sun, Mar 20, 2011 at 08:14:21PM -0400, Noah Misch wrote:
> > > Agreed.  The documentation is suggestive of this limit:
> > > 
> > > # CREATE TABLE n (c numeric(1001,0));
> > > ERROR:  NUMERIC precision 1001 must be between 1 and 1000
> > > LINE 1: CREATE TABLE n (c numeric(1001,0));
> > > 
> > > However, that's indeed just a limit of the numeric typmod representation, not
> > > the data type itself.  An unqualified "numeric" column hits no such limit.
> > 
> > For the record, the limits I found from my tests are:
> > * 2^17 - 1 maximum total digits

The implementation limit isn't on total digits, but rather on digits before the
decimal point ("weight") and digits after ("dscale") separately.  The largest
possible numeric is 10^(2^17) - 10^(-(2^14 - 1)), which has 2^17 + 2^14 - 1
total digits.  You can generate it with:
 SELECT (repeat('9', 131072) || '.' || repeat('9', 16383))::numeric;

> > * 2^14 - 1 maximum fractional digits

ACK.

> > 
> > (I did tests as I couldn't extract any obvious limit from the source
> > code of numeric.c)

NumericLong has a 14-bit count of decimal digits for the dscale, giving that
fractional digit limit.  It stores the weight as a 16-bit signed count of
base-10000 "digits" after the first.  For example, 10^4-1 has weight 0, 10^4
through 10^8 - 1 have weight 1, 10^8 has weight 2, etc.  For purposes of hitting
the positive limit, we have 15 bits of weight.  Therefore, it can represent up
to 2^15 * 4 = 2^17 digits.

> --- a/doc/src/sgml/datatype.sgml
> +++ b/doc/src/sgml/datatype.sgml

There's a table further up on this page that lists of the range of each type,
with "no limit" listed for numeric.  It could use an update noting with the
supported extremes and fractional digit limit.

> @@ -476,7 +476,7 @@
>      </indexterm>
>  
>      <para>
> -     The type <type>numeric</type> can store numbers with up to 1000
> +     The type <type>numeric</type> can store numbers with up to 131071
>       digits of precision and perform calculations exactly. It is

Since there's no simple limit on precision, let's remove this note about it and
let the range description in the table above cover that matter.

>       especially recommended for storing monetary amounts and other
>       quantities where exactness is required. However, arithmetic on
> @@ -493,7 +493,7 @@
>       the whole number, that is, the number of digits to both sides of
>       the decimal point.  So the number 23.5141 has a precision of 6
>       and a scale of 4.  Integers can be considered to have a scale of
> -     zero.
> +     zero. The maximum allowed scale is 16383.
>      </para>
>  
>      <para>
> @@ -525,6 +525,15 @@ NUMERIC
>       explicitly.)
>      </para>
>  
> +    <note>
> +     <para>
> +      The maximum allowed precision when explicitely specified in the
> +      type declaration is 1000; otherwise the <type>NUMERIC</type>
> +      data type supports a maximum precision of 131071 and a maximum
> +      scale of 16383.
> +     </para>
> +    </note>
> +

Likewise, we can't quote a general precision limit here.

Thanks,
nm