Thread: Arbitrary precision arithmatic with pgsql
Hi, The docs says that numeric type supports numbers upto any precision <docs> 8.1.2. Arbitrary Precision NumbersThe type numeric can store numbers with up to 1000 digits of precision and perform calculations exactly. It is especially recommended for storing monetary amounts and other quantities where exactness is required. However, the numeric type is very slow compared to the floating-point types described in the next section. </docs> However tradein_clients=# SELECT cast(2^100 as numeric); +---------------------------------+ | numeric | +---------------------------------+ | 1267650600228230000000000000000 | +---------------------------------+ (1 row) Time: 1036.063 ms Naturally there is a loss of information here. So my question is 1. Does the specs not require pgsql to print a warning or info , will it not be considered silient truncation of data. 2. Is there any way to do such calculation using pgsql, i understand bc is a better tool for it. Warm Regards Rajesh Kumar Mallah. -- regds Mallah. Rajesh Kumar Mallah +---------------------------------------------------+ | Tradeindia.com (3,11,246) Registered Users | | Indias' Leading B2B eMarketPlace | | http://www.tradeindia.com/ | +---------------------------------------------------+
On Aug 31, 2004, at 8:55 PM, Rajesh Kumar Mallah wrote: > The docs says that numeric type supports numbers upto > any precision <snip /> > However > > tradein_clients=# SELECT cast(2^100 as numeric); <snip /> > 1. Does the specs not require pgsql to print a warning or info , > will it not be considered silient truncation of data. AFAICS, the issue here is not the cast per se, but rather the power operation (2^100), which expects a double precision argument. This operation happens before the cast. > 2. Is there any way to do such calculation using pgsql, i understand > bc is a better tool for it. What you need is a power operation for numeric, which I think you'd have to write yourself, possibly leveraging one of the procedural languages (perhaps pl/perl) to access such an operation (as you yourself mentioned). I'm sure you could find an algorithm to port to PL/pgsql as well. Hope this helps. Michael Glaesemann grzm myrealbox com
Michael Glaesemann wrote: > > On Aug 31, 2004, at 8:55 PM, Rajesh Kumar Mallah wrote: > >> The docs says that numeric type supports numbers upto >> any precision > > > <snip /> > >> However >> >> tradein_clients=# SELECT cast(2^100 as numeric); > > > <snip /> > >> 1. Does the specs not require pgsql to print a warning or info , >> will it not be considered silient truncation of data. > > > AFAICS, the issue here is not the cast per se, but rather the power > operation (2^100), which expects a double precision argument. This > operation happens before the cast. Looks like the power operation of numeric ie, numeric ^ numeric already exists but it returns a double precision and the accuracy is getting lost. Shud numeric ^ numeric not be returning numeric instead? Regds mallah. tradein_clients=# CREATE TABLE t_a as SELECT 1::numeric ^ 1::numeric as col; SELECT tradein_clients=# \d t_a Table "public.t_a" +--------+------------------+-----------+ | Column | Type | Modifiers | +--------+------------------+-----------+ | col | double precision | | +--------+------------------+-----------+ > >> 2. Is there any way to do such calculation using pgsql, i understand >> bc is a better tool for it. > > > What you need is a power operation for numeric, which I think you'd > have to write yourself, possibly leveraging one of the procedural > languages (perhaps pl/perl) to access such an operation (as you > yourself mentioned). I'm sure you could find an algorithm to port to > PL/pgsql as well. > > Hope this helps. > > Michael Glaesemann > grzm myrealbox com > > > !DSPAM:41346ebe315451222497446! > > -- regds Mallah. Rajesh Kumar Mallah +---------------------------------------------------+ | Tradeindia.com (3,11,246) Registered Users | | Indias' Leading B2B eMarketPlace | | http://www.tradeindia.com/ | +---------------------------------------------------+
On Aug 31, 2004, at 9:17 PM, Michael Glaesemann wrote: > What you need is a power operation for numeric, which I think you'd > have to write yourself, Looking a little closer, there is a pow() function that takes two numeric arguments and returns numeric. <http://www.postgresql.org/docs/current/static/functions-math.html> test=# select pow(2::numeric,100::numeric); pow -------------------------------------------------- 1267650600228229401496703205376.0000000000000000 (1 row) Sorry for the misinformation. If you'd like, I think you can overload the ^ operator to work on numeric as well if you don't want to use pow(). See the following page for more information. <http://www.postgresql.org/docs/current/static/sql-createoperator.html> Michael Glaesemann grzm myrealbox com
Michael Glaesemann wrote: > > On Aug 31, 2004, at 9:17 PM, Michael Glaesemann wrote: > >> What you need is a power operation for numeric, which I think you'd >> have to write yourself, > > > Looking a little closer, there is a pow() function that takes two > numeric arguments and returns numeric. > > <http://www.postgresql.org/docs/current/static/functions-math.html> > > test=# select pow(2::numeric,100::numeric); > pow > -------------------------------------------------- > 1267650600228229401496703205376.0000000000000000 > (1 row) > > Sorry for the misinformation. > > If you'd like, I think you can overload the ^ operator to work on > numeric as well if you don't want to use pow(). See the following page > for more information. > > <http://www.postgresql.org/docs/current/static/sql-createoperator.html> Yep thats cool. Thanks for the research! but i still wonder if a warning or info message were appropriate at some stage so that people do not confuse it with sielent loss of accuracy . I know this example is *not* a case of where postgresql is truncating data at the insert level (like mysql does) but at the calculation level. regds mallah. regds mallah. > > Michael Glaesemann > grzm myrealbox com > > > !DSPAM:4134745e87571738116768! > > -- regds Mallah. Rajesh Kumar Mallah +---------------------------------------------------+ | Tradeindia.com (3,11,246) Registered Users | | Indias' Leading B2B eMarketPlace | | http://www.tradeindia.com/ | +---------------------------------------------------+
On 8/31/2004 9:15 AM, Rajesh Kumar Mallah wrote: > Michael Glaesemann wrote: > >> >> On Aug 31, 2004, at 9:17 PM, Michael Glaesemann wrote: >> >>> What you need is a power operation for numeric, which I think you'd >>> have to write yourself, >> >> >> Looking a little closer, there is a pow() function that takes two >> numeric arguments and returns numeric. >> >> <http://www.postgresql.org/docs/current/static/functions-math.html> >> >> test=# select pow(2::numeric,100::numeric); >> pow >> -------------------------------------------------- >> 1267650600228229401496703205376.0000000000000000 >> (1 row) >> >> Sorry for the misinformation. >> >> If you'd like, I think you can overload the ^ operator to work on >> numeric as well if you don't want to use pow(). See the following page >> for more information. >> >> <http://www.postgresql.org/docs/current/static/sql-createoperator.html> > > > Yep thats cool. Thanks for the research! > > but i still wonder if a warning or info message were > appropriate at some stage so that people do not confuse it > with sielent loss of accuracy . I know this example is *not* a > case of where postgresql is truncating data at the insert level > (like mysql does) but at the calculation level. I agree that doing select 2::numeric ^ 100; should emit some sort of a warning. Because what happens here is that the numeric value is degraded to a float8 in order to use the operator. I don't think that select 2 ^ 100; should emit the same warning. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
On Aug 31, 2004, at 11:18 PM, Jan Wieck wrote: > I agree that doing > > select 2::numeric ^ 100; > > should emit some sort of a warning. Because what happens here is that > the numeric value is degraded to a float8 in order to use the > operator. Would this be solved by overloading the ^ operator with the pow(numeric,numeric) function? Would the 100 be cast INT -> NUMERIC? Michael Glaesemann grzm myrealbox com
Jan Wieck <JanWieck@Yahoo.com> writes:
> I agree that doing
> select 2::numeric ^ 100;
> should emit some sort of a warning.
I do not. The conversion of 2::numeric to float is exact, so AFAICS
the only way to do that would be to make *every* coercion of numeric to
float emit a warning. This is not a reasonable response to the fact
that Rajesh is unaware of the set of available operators. Moreover
it would essentially break float constants (since "2.0" starts life
as numeric and is only cast to float when the context demands).
I'd be in favor of adding a numeric^numeric operator to ease access to
the existing pow() code, but I'm not sure this makes the issue go away
entirely. It looks like "select 2 ^ 100" would still default to being
a float operation.
regression=# create operator ^ (procedure=pow, leftarg=numeric, rightarg=numeric);
CREATE OPERATOR
regression=# select 2::numeric ^ 100; ?column?
--------------------------------------------------1267650600228229401496703205376.0000000000000000
(1 row)
regression=# select 2 ^ 100; ?column?
----------------------1.26765060022823e+30
(1 row)
regression=# select 2.0 ^ 100; ?column?
--------------------------------------------------1267650600228229401496703205376.0000000000000000
(1 row)
regards, tom lane
On 8/31/2004 11:04 AM, Tom Lane wrote: > Jan Wieck <JanWieck@Yahoo.com> writes: >> I agree that doing >> select 2::numeric ^ 100; >> should emit some sort of a warning. > > I do not. The conversion of 2::numeric to float is exact, so AFAICS > the only way to do that would be to make *every* coercion of numeric to > float emit a warning. This is not a reasonable response to the fact > that Rajesh is unaware of the set of available operators. Moreover > it would essentially break float constants (since "2.0" starts life > as numeric and is only cast to float when the context demands). I thought they start life as an unknown literal ... that of course changes things. Jan > > I'd be in favor of adding a numeric^numeric operator to ease access to > the existing pow() code, but I'm not sure this makes the issue go away > entirely. It looks like "select 2 ^ 100" would still default to being > a float operation. > > regression=# create operator ^ (procedure=pow, leftarg=numeric, rightarg=numeric); > CREATE OPERATOR > regression=# select 2::numeric ^ 100; > ?column? > -------------------------------------------------- > 1267650600228229401496703205376.0000000000000000 > (1 row) > > regression=# select 2 ^ 100; > ?column? > ---------------------- > 1.26765060022823e+30 > (1 row) > > regression=# select 2.0 ^ 100; > ?column? > -------------------------------------------------- > 1267650600228229401496703205376.0000000000000000 > (1 row) > > > regards, tom lane -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #