Skip site navigation (1)
Skip section navigation (2)
## Re: maximum digits for NUMERIC

### In response to

### Responses

### pgsql-hackers by date

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

- Re: maximum digits for NUMERIC at 2011-03-25 18:09:54 from Gianni Ciolli

- Re: maximum digits for NUMERIC at 2011-04-01 10:44:23 from Gianni Ciolli

Next: From:Dave PageDate:2011-04-01 08:24:00Subject: Re: Should psql support URI syntax?Previous: From: Adrian von BidderDate: 2011-04-01 07:12:54Subject: Re: Should psql support URI syntax?