Skip site navigation (1) Skip section navigation (2)

Re: Reduce NUMERIC size by 2 bytes, reduce max length to 508 digits

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
Cc: Simon Riggs <simon(at)2ndquadrant(dot)com>, PostgreSQL-general <pgsql-general(at)postgresql(dot)org>
Subject: Re: Reduce NUMERIC size by 2 bytes, reduce max length to 508 digits
Date: 2005-12-02 20:37:56
Message-ID: 6797.1133555876@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-hackerspgsql-patches
Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> There is a patch under consideration for 8.2 that would reduce the
> storage requirement for numeric values by two bytes, but also reduce the
> range of allowed numeric values to 508 digits.  The current specified
> maximum NUMERIC length is 1000 (NUMERIC(1000,0)), and the maximum
> computational length is 4096 digits.  (Computations over 4096 digits are
> silently truncated.

Bruce is entirely incorrect on the last two points (although he may have
found a bug in his copy of psql, see thread in -patches if you care).

The actual effective limit on NUMERIC is presently 10^128K, which is
probably enough to count the femtoseconds remaining until the heat death
of the universe, and then multiply that by the number of elementary
particles therein ;-).  And it is checked --- compare what you get from
	select pow(10::numeric, 131071);
	select pow(10::numeric, 131072);

Restricting NUMERIC to 10^508 would therefore be a significant reduction
in dynamic range.  Whether anyone seriously cares is another question
--- if you do want unlimited-precision arithmetic, you should probably
be doing it in some other software anyway.  (The NUMERIC routines get
painfully slow with tens of thousands of digits :-(.)

The current 1000-digit limit on declared NUMERIC columns is basically an
artificial limit, with pretty much the same reasoning as the artificial
limit on declared VARCHAR length: if you think you need more than 1000
digits then you probably ought not be declaring a specific upper limit
at all.

BTW, the limit is on dynamic range, not number of digits: the first
significant digit has to be within 128K places of the decimal point
(or, if this patch is applied, within 508 places of the decimal point),
but you can have as many digits as you like after that one.  It would be
reasonable to describe the patched system as allowing 500 places before
and 500 places after the decimal point, or 1000 digits overall.

So the question is, is anyone doing anything with Postgres that would be
affected by a 500-place limit, or even come close to being affected?

			regards, tom lane

In response to

Responses

pgsql-hackers by date

Next:From: Bruce MomjianDate: 2005-12-02 20:44:11
Subject: Re: Numeric 508 datatype
Previous:From: Joshua D. DrakeDate: 2005-12-02 20:27:41
Subject: Re: [HACKERS] Should libedit be preferred to libreadline?

pgsql-patches by date

Next:From: Bruce MomjianDate: 2005-12-02 20:44:11
Subject: Re: Numeric 508 datatype
Previous:From: Joshua D. DrakeDate: 2005-12-02 20:27:41
Subject: Re: [HACKERS] Should libedit be preferred to libreadline?

pgsql-general by date

Next:From: Tom LaneDate: 2005-12-02 20:40:30
Subject: Re: Reduce NUMERIC size by 2 bytes, reduce max length to 508 digits
Previous:From: Will GlynnDate: 2005-12-02 20:29:32
Subject: Re: memory leak under heavy load?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group