Обсуждение: 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.ciolli@2ndquadrant.it | www.2ndquadrant.it
Gianni Ciolli <gianni.ciolli@2ndquadrant.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.ciolli@2ndquadrant.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.ciolli@2ndquadrant.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.ciolli@2ndquadrant.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.ciolli@2ndquadrant.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.ciolli@2ndquadrant.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@leadboat.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@leadboat.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@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml new file mode 100644 index 13b888d..74408b0 *** a/doc/src/sgml/datatype.sgml --- b/doc/src/sgml/datatype.sgml *************** NUMERIC *** 529,536 **** <para> The maximum allowed precision when explicitely specified in the type declaration is 1000; otherwise the current implementation ! of the <type>NUMERIC</type> is subject to the limits described ! in <xref linkend="datatype-numeric-table">. </para> </note> --- 529,537 ---- <para> The maximum allowed precision when explicitely specified in the type declaration is 1000; otherwise the current implementation ! of <type>NUMERIC</type> (when the precision is not specified) ! is subject to the limits described in <xref ! linkend="datatype-numeric-table">. </para> </note>
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@commandprompt.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@momjian.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@momjian.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