Arbitrary precision arithmatic with pgsql

Hi,

The docs says that numeric type supports numbers upto
any precision


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.



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/ |
+---------------------------------------------------+


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Re: Arbitrary precision arithmatic with pgsql

On Aug 31, 2004, at 8:55 PM, Rajesh Kumar Mallah wrote:

> The docs says that numeric type supports numbers upto
> any precision



> However
>
> tradein_clients=# SELECT cast(2^100 as numeric);



> 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


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Re: Arbitrary precision arithmatic with pgsql

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
>
>
>
>
>> However
>>
>> tradein_clients=# SELECT cast(2^100 as numeric);
>
>
>
>
>> 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/ |
+---------------------------------------------------+


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Re: Arbitrary precision arithmatic with pgsql

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.



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.



Michael Glaesemann
grzm myrealbox com


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Re: Arbitrary precision arithmatic with pgsql

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.
>
>
>
> 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.
>
>


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/ |
+---------------------------------------------------+


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Re: Arbitrary precision arithmatic with pgsql

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.
>>
>>
>>
>> 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.
>>
>>
>
>
> 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 #

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Re: Arbitrary precision arithmatic with pgsql

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


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Re: Arbitrary precision arithmatic with pgsql

Jan Wieck 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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Re: Arbitrary precision arithmatic with pgsql

On 8/31/2004 11:04 AM, Tom Lane wrote:

> Jan Wieck 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 #

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings