Re: maximum digits for NUMERIC

Hi,

maybe we should change the "1000 digits" here:

http://developer.postgresql.org/pgdocs/postgres/datatype-numeric.html#DATATYPE-NUMERIC-DECIMAL

because ISTM that up to 2^17 digits are supported (which makes more
sense than 1000).

Best regards,
Dr. Gianni Ciolli - 2ndQuadrant Italia
PostgreSQL Training, Services and Support
gianni(dot)ciolli(at)2ndquadrant(dot)it | www.2ndquadrant.it

Gianni Ciolli <gianni(dot)ciolli(at)2ndquadrant(dot)it> writes:
> maybe we should change the "1000 digits" here:

> http://developer.postgresql.org/pgdocs/postgres/datatype-numeric.html#DATATYPE-NUMERIC-DECIMAL

> because ISTM that up to 2^17 digits are supported

This is incorrect. (You're confusing the number of stored digits
with the location of the decimal point.)

regards, tom lane

On Fri, Mar 11, 2011 at 09:38:03AM -0500, Tom Lane wrote:
> Gianni Ciolli <gianni(dot)ciolli(at)2ndquadrant(dot)it> writes:
> > maybe we should change the "1000 digits" here:
>
> > http://developer.postgresql.org/pgdocs/postgres/datatype-numeric.html#DATATYPE-NUMERIC-DECIMAL
>
> > because ISTM that up to 2^17 digits are supported
>
> This is incorrect. (You're confusing the number of stored digits
> with the location of the decimal point.)

My understanding of the documentation is that precision is defined as
"the total count of significant digits in the whole number" while the
location of the decimal point can be determined by the scale, which is
defined as "the count of decimal digits in the fractional part, to the
right of the decimal point".

The documentation I mentioned previously starts with

"The type numeric can store numbers with up to 1000 digits of
precision and perform calculations exactly."

and I was able to store a base 10 integers with up to 2^17 digits in a
NUMERIC; so I still believe that the documentation is incorrect in
saying that (by my understanding of the definition of significant
digits in an exact integer).

If there is a limit of 1000 on the number of fractional digits to the
right of the decimal points, then we should change that wording
(unfortunately I won't be able to run this test before UTC+0 evening).

Best regards,
Dr. Gianni Ciolli - 2ndQuadrant Italia
PostgreSQL Training, Services and Support
gianni(dot)ciolli(at)2ndquadrant(dot)it | www.2ndquadrant.it

p.s. my small investigation started from having read the
documentation, having incorrectly believed that NUMERIC would
have rejected integers greater than 10^1000, and finding
experimentally that the threshold is about 10^(2^17) (all with
9.0).

On Fri, Mar 11, 2011 at 11:36:14AM +0000, Gianni Ciolli wrote:
> maybe we should change the "1000 digits" here:
>
> http://developer.postgresql.org/pgdocs/postgres/datatype-numeric.html#DATATYPE-NUMERIC-DECIMAL
>
> because ISTM that up to 2^17 digits are supported (which makes more
> sense than 1000).

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.

On Sun, Mar 20, 2011 at 08:14:21PM -0400, Noah Misch wrote:
> On Fri, Mar 11, 2011 at 11:36:14AM +0000, Gianni Ciolli wrote:
> > maybe we should change the "1000 digits" here:
> >
> > http://developer.postgresql.org/pgdocs/postgres/datatype-numeric.html#DATATYPE-NUMERIC-DECIMAL
> >
> > because ISTM that up to 2^17 digits are supported (which makes more
> > sense than 1000).
>
> 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
* 2^14 - 1 maximum fractional digits

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

Best regards,
Dr. Gianni Ciolli - 2ndQuadrant Italia
PostgreSQL Training, Services and Support
gianni(dot)ciolli(at)2ndquadrant(dot)it | www.2ndquadrant.it

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
> * 2^14 - 1 maximum fractional digits
>
> (I did tests as I couldn't extract any obvious limit from the source
> code of numeric.c)

The attached patch resumes this short discussion.

Best regards,
Dr. Gianni Ciolli - 2ndQuadrant Italia
PostgreSQL Training, Services and Support
gianni(dot)ciolli(at)2ndquadrant(dot)it | www.2ndquadrant.it

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

On Fri, Apr 01, 2011 at 03:52:22AM -0400, Noah Misch wrote:

> 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.

OK; thanks also for the explaination.

>
> > --- 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.

OK.

Please find attached v2 of the numeric-doc patch, which takes into
account your remarks. In particular, numeric limits are now correct
and documented only in that table.

Best regards,
Dr. Gianni Ciolli - 2ndQuadrant Italia
PostgreSQL Training, Services and Support
gianni(dot)ciolli(at)2ndquadrant(dot)it | www.2ndquadrant.it

On Fri, Apr 01, 2011 at 11:44:23AM +0100, Gianni Ciolli wrote:
> Please find attached v2 of the numeric-doc patch, which takes into
> account your remarks. In particular, numeric limits are now correct
> and documented only in that table.

This version looks sound to me. Thank you.

On Fri, Apr 1, 2011 at 7:51 AM, Noah Misch <noah(at)leadboat(dot)com> wrote:
> On Fri, Apr 01, 2011 at 11:44:23AM +0100, Gianni Ciolli wrote:
>> Please find attached v2 of the numeric-doc patch, which takes into
>> account your remarks. In particular, numeric limits are now correct
>> and documented only in that table.
>
> This version looks sound to me.  Thank you.

Committed.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas wrote:
> On Fri, Apr 1, 2011 at 7:51 AM, Noah Misch <noah(at)leadboat(dot)com> wrote:
> > On Fri, Apr 01, 2011 at 11:44:23AM +0100, Gianni Ciolli wrote:
> >> Please find attached v2 of the numeric-doc patch, which takes into
> >> account your remarks. In particular, numeric limits are now correct
> >> and documented only in that table.
> >
> > This version looks sound to me. ?Thank you.
>
> Committed.

Wow, I am so glad someone documented this. I often do factorial(4000)
which generates 12673 digits when teaching classes, and it bugged me
that we documented the limit as 1000 digits. I had asked about
improving the docs years ago and was discouraged because people thought
we might someday want to limit the length to 1000. Do we want to bump
up that specified limit?

The attached, applied patch clarifies that it is non-precision-specified
NUMERIC that has a very high range.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

Excerpts from Bruce Momjian's message of mar abr 26 12:58:19 -0300 2011:

> Wow, I am so glad someone documented this. I often do factorial(4000)
> which generates 12673 digits when teaching classes, and it bugged me
> that we documented the limit as 1000 digits.

I keep wondering why you want to know factorial(4000) so often.

--
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Alvaro Herrera wrote:
> Excerpts from Bruce Momjian's message of mar abr 26 12:58:19 -0300 2011:
>
> > Wow, I am so glad someone documented this. I often do factorial(4000)
> > which generates 12673 digits when teaching classes, and it bugged me
> > that we documented the limit as 1000 digits.
>
> I keep wondering why you want to know factorial(4000) so often.

It is just to impress folks, and it is impressive. An instant
screenful of digits is pretty cool.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

On Wed, Apr 27, 2011 at 4:47 AM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> Alvaro Herrera wrote:
>> Excerpts from Bruce Momjian's message of mar abr 26 12:58:19 -0300 2011:
>>
>> > Wow, I am so glad someone documented this.  I often do factorial(4000)
>> > which generates 12673 digits when teaching classes, and it bugged me
>> > that we documented the limit as 1000 digits.
>>
>> I keep wondering why you want to know factorial(4000) so often.
>
> It is just to impress folks, and it is impressive.  An instant
> screenful of digits is pretty cool.

If you are into impressing people with big numbers (or maybe doing
something useful with them too) you may take a look at
http://pgmp.projects.postgresql.org/

-- Daniele