Thread: Money

I' m trying to perform some simple arithmetic with a field datatype,
money.

For example, select part_id, cost from parts;  gives the id and cost
in dollars...
         select part_id, cost+1.00 from parts; ....adds $1.00 to
displayed cost.
         select part_id, cost*2 from parts; produces an error message...
"There is no operator '*' for types money.....retype this query  using
an explicit cast or.....define the operator using CREATE OPERATOR."

If I examine the list of operators using \do,  there are any number of
them shown for *, money, int2, float4, etc.

Question 1:  Is this a list of existing oeprators or a list of
permitted ones that I have to create?

Question 2:  I can't figure out haw to use the CAST ::   ....Any
hints?

Again, TIA to one and all.

.......Bill Sneed....Prospect, Maine......

Hey there,

I had some similar problems recently, try this

select someid, somemoneyfield * 2::float as product from table;

You cannot multiply money * money, apparently.  But, money * float seems
to work fine.  Same goes with division:

select someid, somemoneyfield / 2::float as quotient from table;

Hope this helps.


On Sat, 12 Dec 1998, Bill Sneed wrote:

> Date: Sat, 12 Dec 1998 22:09:19 +0000
> From: Bill Sneed <bsneed@mint.net>
> To: "pgsql-general@hub.org" <pgsql-general@hub.org>
> Subject: [GENERAL] Money
>
> I' m trying to perform some simple arithmetic with a field datatype,
> money.
>
> For example, select part_id, cost from parts;  gives the id and cost
> in dollars...
>          select part_id, cost+1.00 from parts; ....adds $1.00 to
> displayed cost.
>          select part_id, cost*2 from parts; produces an error message...
> "There is no operator '*' for types money.....retype this query  using
> an explicit cast or.....define the operator using CREATE OPERATOR."
>
> If I examine the list of operators using \do,  there are any number of
> them shown for *, money, int2, float4, etc.
>
> Question 1:  Is this a list of existing oeprators or a list of
> permitted ones that I have to create?
>
> Question 2:  I can't figure out haw to use the CAST ::   ....Any
> hints?
>
> Again, TIA to one and all.
>
> .......Bill Sneed....Prospect, Maine......
>
>
>

Bob Dusek wrote:
>
> Hey there,
>
> I had some similar problems recently, try this
>
> select someid, somemoneyfield * 2::float as product from table;
>
> You cannot multiply money * money, apparently.  But, money * float seems
> to work fine.  Same goes with division:
>
> select someid, somemoneyfield / 2::float as quotient from table;
>
> Hope this helps.
>
>
Thanks Bob...to some extent it does...I was able to do ---
somemoneyfield *2::int2 or /2::int2 but got a complaint when trying a
floating point datatype....but if it works for you it must be me!  :-)

....TNX...Bill