pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedArbitrary precision arithmatic with pgsql
9+ messages / 4 participants
[nested] [flat]
* Arbitrary precision arithmatic with pgsql
@ 2004-08-31 11:55 Rajesh Kumar Mallah <mallah@trade-india.com>
2004-08-31 12:17 ` Re: Arbitrary precision arithmatic with pgsql Michael Glaesemann <grzm@myrealbox.com>
0 siblings, 1 reply; 9+ messages in thread
From: Rajesh Kumar Mallah @ 2004-08-31 11:55 UTC (permalink / raw)
To: pgsql-sql
Hi,
The docs says that numeric type supports numbers upto
any precision
<docs>
8.1.2. Arbitrary Precision Numbers
The 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/ |
+---------------------------------------------------+
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Arbitrary precision arithmatic with pgsql
2004-08-31 11:55 Arbitrary precision arithmatic with pgsql Rajesh Kumar Mallah <mallah@trade-india.com>
@ 2004-08-31 12:17 ` Michael Glaesemann <grzm@myrealbox.com>
2004-08-31 12:37 ` Re: Arbitrary precision arithmatic with pgsql Rajesh Kumar Mallah <mallah@trade-india.com>
2004-08-31 12:41 ` Re: Arbitrary precision arithmatic with pgsql Michael Glaesemann <grzm@myrealbox.com>
0 siblings, 2 replies; 9+ messages in thread
From: Michael Glaesemann @ 2004-08-31 12:17 UTC (permalink / raw)
To: Rajesh Kumar Mallah <mallah@trade-india.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Arbitrary precision arithmatic with pgsql
2004-08-31 11:55 Arbitrary precision arithmatic with pgsql Rajesh Kumar Mallah <mallah@trade-india.com>
2004-08-31 12:17 ` Re: Arbitrary precision arithmatic with pgsql Michael Glaesemann <grzm@myrealbox.com>
@ 2004-08-31 12:37 ` Rajesh Kumar Mallah <mallah@trade-india.com>
1 sibling, 0 replies; 9+ messages in thread
From: Rajesh Kumar Mallah @ 2004-08-31 12:37 UTC (permalink / raw)
To: Michael Glaesemann <grzm@myrealbox.com>; +Cc: pgsql-sql
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/ |
+---------------------------------------------------+
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Arbitrary precision arithmatic with pgsql
2004-08-31 11:55 Arbitrary precision arithmatic with pgsql Rajesh Kumar Mallah <mallah@trade-india.com>
2004-08-31 12:17 ` Re: Arbitrary precision arithmatic with pgsql Michael Glaesemann <grzm@myrealbox.com>
@ 2004-08-31 12:41 ` Michael Glaesemann <grzm@myrealbox.com>
2004-08-31 13:15 ` Re: Arbitrary precision arithmatic with pgsql Rajesh Kumar Mallah <mallah@trade-india.com>
1 sibling, 1 reply; 9+ messages in thread
From: Michael Glaesemann @ 2004-08-31 12:41 UTC (permalink / raw)
To: Michael Glaesemann <grzm@myrealbox.com>; +Cc: Rajesh Kumar Mallah <mallah@trade-india.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Arbitrary precision arithmatic with pgsql
2004-08-31 11:55 Arbitrary precision arithmatic with pgsql Rajesh Kumar Mallah <mallah@trade-india.com>
2004-08-31 12:17 ` Re: Arbitrary precision arithmatic with pgsql Michael Glaesemann <grzm@myrealbox.com>
2004-08-31 12:41 ` Re: Arbitrary precision arithmatic with pgsql Michael Glaesemann <grzm@myrealbox.com>
@ 2004-08-31 13:15 ` Rajesh Kumar Mallah <mallah@trade-india.com>
2004-08-31 14:18 ` Re: Arbitrary precision arithmatic with pgsql Jan Wieck <JanWieck@Yahoo.com>
0 siblings, 1 reply; 9+ messages in thread
From: Rajesh Kumar Mallah @ 2004-08-31 13:15 UTC (permalink / raw)
To: Michael Glaesemann <grzm@myrealbox.com>; +Cc: pgsql-sql
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/ |
+---------------------------------------------------+
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Arbitrary precision arithmatic with pgsql
2004-08-31 11:55 Arbitrary precision arithmatic with pgsql Rajesh Kumar Mallah <mallah@trade-india.com>
2004-08-31 12:17 ` Re: Arbitrary precision arithmatic with pgsql Michael Glaesemann <grzm@myrealbox.com>
2004-08-31 12:41 ` Re: Arbitrary precision arithmatic with pgsql Michael Glaesemann <grzm@myrealbox.com>
2004-08-31 13:15 ` Re: Arbitrary precision arithmatic with pgsql Rajesh Kumar Mallah <mallah@trade-india.com>
@ 2004-08-31 14:18 ` Jan Wieck <JanWieck@Yahoo.com>
2004-08-31 14:27 ` Re: Arbitrary precision arithmatic with pgsql Michael Glaesemann <grzm@myrealbox.com>
2004-08-31 15:04 ` Re: Arbitrary precision arithmatic with pgsql Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 9+ messages in thread
From: Jan Wieck @ 2004-08-31 14:18 UTC (permalink / raw)
To: Rajesh Kumar Mallah <mallah@trade-india.com>; +Cc: Michael Glaesemann <grzm@myrealbox.com>; pgsql-sql
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 #
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Arbitrary precision arithmatic with pgsql
2004-08-31 11:55 Arbitrary precision arithmatic with pgsql Rajesh Kumar Mallah <mallah@trade-india.com>
2004-08-31 12:17 ` Re: Arbitrary precision arithmatic with pgsql Michael Glaesemann <grzm@myrealbox.com>
2004-08-31 12:41 ` Re: Arbitrary precision arithmatic with pgsql Michael Glaesemann <grzm@myrealbox.com>
2004-08-31 13:15 ` Re: Arbitrary precision arithmatic with pgsql Rajesh Kumar Mallah <mallah@trade-india.com>
2004-08-31 14:18 ` Re: Arbitrary precision arithmatic with pgsql Jan Wieck <JanWieck@Yahoo.com>
@ 2004-08-31 14:27 ` Michael Glaesemann <grzm@myrealbox.com>
1 sibling, 0 replies; 9+ messages in thread
From: Michael Glaesemann @ 2004-08-31 14:27 UTC (permalink / raw)
To: Jan Wieck <JanWieck@Yahoo.com>; +Cc: Rajesh Kumar Mallah <mallah@trade-india.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Arbitrary precision arithmatic with pgsql
2004-08-31 11:55 Arbitrary precision arithmatic with pgsql Rajesh Kumar Mallah <mallah@trade-india.com>
2004-08-31 12:17 ` Re: Arbitrary precision arithmatic with pgsql Michael Glaesemann <grzm@myrealbox.com>
2004-08-31 12:41 ` Re: Arbitrary precision arithmatic with pgsql Michael Glaesemann <grzm@myrealbox.com>
2004-08-31 13:15 ` Re: Arbitrary precision arithmatic with pgsql Rajesh Kumar Mallah <mallah@trade-india.com>
2004-08-31 14:18 ` Re: Arbitrary precision arithmatic with pgsql Jan Wieck <JanWieck@Yahoo.com>
@ 2004-08-31 15:04 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-08-31 15:12 ` Re: Arbitrary precision arithmatic with pgsql Jan Wieck <JanWieck@Yahoo.com>
1 sibling, 1 reply; 9+ messages in thread
From: Tom Lane @ 2004-08-31 15:04 UTC (permalink / raw)
To: Jan Wieck <JanWieck@Yahoo.com>; +Cc: Rajesh Kumar Mallah <mallah@trade-india.com>; Michael Glaesemann <grzm@myrealbox.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Arbitrary precision arithmatic with pgsql
2004-08-31 11:55 Arbitrary precision arithmatic with pgsql Rajesh Kumar Mallah <mallah@trade-india.com>
2004-08-31 12:17 ` Re: Arbitrary precision arithmatic with pgsql Michael Glaesemann <grzm@myrealbox.com>
2004-08-31 12:41 ` Re: Arbitrary precision arithmatic with pgsql Michael Glaesemann <grzm@myrealbox.com>
2004-08-31 13:15 ` Re: Arbitrary precision arithmatic with pgsql Rajesh Kumar Mallah <mallah@trade-india.com>
2004-08-31 14:18 ` Re: Arbitrary precision arithmatic with pgsql Jan Wieck <JanWieck@Yahoo.com>
2004-08-31 15:04 ` Re: Arbitrary precision arithmatic with pgsql Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-08-31 15:12 ` Jan Wieck <JanWieck@Yahoo.com>
0 siblings, 0 replies; 9+ messages in thread
From: Jan Wieck @ 2004-08-31 15:12 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Rajesh Kumar Mallah <mallah@trade-india.com>; Michael Glaesemann <grzm@myrealbox.com>; pgsql-sql
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 #
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2004-08-31 15:12 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-08-31 11:55 Arbitrary precision arithmatic with pgsql Rajesh Kumar Mallah <mallah@trade-india.com>
2004-08-31 12:17 ` Michael Glaesemann <grzm@myrealbox.com>
2004-08-31 12:37 ` Rajesh Kumar Mallah <mallah@trade-india.com>
2004-08-31 12:41 ` Michael Glaesemann <grzm@myrealbox.com>
2004-08-31 13:15 ` Rajesh Kumar Mallah <mallah@trade-india.com>
2004-08-31 14:18 ` Jan Wieck <JanWieck@Yahoo.com>
2004-08-31 14:27 ` Michael Glaesemann <grzm@myrealbox.com>
2004-08-31 15:04 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-08-31 15:12 ` Jan Wieck <JanWieck@Yahoo.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox