Thread: Alternative to "Money" ...

Alternative to "Money" ...

From

Chris Gamache

Date:

30 January 2004, 17:55:15

Well, after living with the (depreciated) Money datatype for 5 years, I think
its finally time to say goodbye (and now that we have DROP COLUMN it'll be MUCH
easier transition). What's the preferred monetary datatype? numeric(10,2)?
float4? float8? We're dealing only with US Dollars.

CG

__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free web site building tool. Try it!
http://webhosting.yahoo.com/ps/sb/

Re: Alternative to "Money" ...

From

Bruno Wolff III

Date:

30 January 2004, 19:30:10

On Fri, Jan 30, 2004 at 10:55:13 -0800,
  Chris Gamache <cgg007@yahoo.com> wrote:
> Well, after living with the (depreciated) Money datatype for 5 years, I think
> its finally time to say goodbye (and now that we have DROP COLUMN it'll be MUCH
> easier transition). What's the preferred monetary datatype? numeric(10,2)?
> float4? float8? We're dealing only with US Dollars.

You probably just want plain numeric.

Re: Alternative to "Money" ...

From

Russell Shaw

Date:

31 January 2004, 03:47:15

Bruno Wolff III wrote:
> On Fri, Jan 30, 2004 at 10:55:13 -0800,
>   Chris Gamache <cgg007@yahoo.com> wrote:
>
>>Well, after living with the (depreciated) Money datatype for 5 years, I think
>>its finally time to say goodbye (and now that we have DROP COLUMN it'll be MUCH
>>easier transition). What's the preferred monetary datatype? numeric(10,2)?
>>float4? float8? We're dealing only with US Dollars.
>
>
> You probably just want plain numeric.

http://www.postgresql.org/docs/7.4/interactive/datatype.html#DATATYPE-NUMERIC-DECIMAL

Re: Alternative to "Money" ...

From

Harald Fuchs

Date:

02 February 2004, 16:01:15

In article <20040130203122.GA1488@wolff.to>,
Bruno Wolff III <bruno@wolff.to> writes:

> On Fri, Jan 30, 2004 at 10:55:13 -0800,
>   Chris Gamache <cgg007@yahoo.com> wrote:
>> Well, after living with the (depreciated) Money datatype for 5 years, I think
>> its finally time to say goodbye (and now that we have DROP COLUMN it'll be MUCH
>> easier transition). What's the preferred monetary datatype? numeric(10,2)?
>> float4? float8? We're dealing only with US Dollars.

> You probably just want plain numeric.

As long as you don't want to deal with the new US budget deficit,
storing cents in an INT or BIGINT column might perform better.

Re: Alternative to "Money" ...

From

Chris Gamache

Date:

03 February 2004, 13:17:58

... I can't _quite_ tell if you're serious or not ... :)

If you are serious, are you saying to do something like:

CREATE TABLE new_money (product text, dollars int4, cents int4);
INSERT INTO new_money (product, dollars, cents) values ('Flowbee','19','95');
INSERT INTO new_money (product, dollars, cents) values ('Garth Brooks\'s
Greatest Hits','9','99');

SELECT product, (dollars || '.' || cents)::numeric FROM new_money;
           product            | numeric
------------------------------+---------
 Flowbee                      |   19.95
 Garth Brooks's Greatest Hits |    9.99
(2 rows)

... Will that really improve performance? I'd probably have to create a view
and rule on the view if I didn't want to drasticly alter the way I'm handling
currency in my pre-existing code ...

CG

--- Harald Fuchs <hf118@protecting.net> wrote:
> In article <20040130203122.GA1488@wolff.to>,
> Bruno Wolff III <bruno@wolff.to> writes:
>
> > On Fri, Jan 30, 2004 at 10:55:13 -0800,
> >   Chris Gamache <cgg007@yahoo.com> wrote:
> >> Well, after living with the (depreciated) Money datatype for 5 years, I
> think
> >> its finally time to say goodbye (and now that we have DROP COLUMN it'll be
> MUCH
> >> easier transition). What's the preferred monetary datatype? numeric(10,2)?
> >> float4? float8? We're dealing only with US Dollars.
>
> > You probably just want plain numeric.
>
> As long as you don't want to deal with the new US budget deficit,
> storing cents in an INT or BIGINT column might perform better.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings


__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free web site building tool. Try it!
http://webhosting.yahoo.com/ps/sb/

Re: Alternative to "Money" ...

From

"Matt Clark"

Date:

03 February 2004, 13:42:54

> .. I can't _quite_ tell if you're serious or not ... :)
>
> If you are serious, are you saying to do something like:
>
> CREATE TABLE new_money (product text, dollars int4, cents int4);

Ha :-)  That would not be serious.  I'm pretty sure he meant to just store the product cost in cents instead of
dollars,e.g. 

> CREATE TABLE new_money (product text, cents int4);
> INSERT INTO new_money (product, cents) values ('Flowbee','1995');
> INSERT INTO new_money (product, cents) values ('Garth Brooks\'s
> Greatest Hits','999');

M

Re: Alternative to "Money" ...

From

Chris Gamache

Date:

03 February 2004, 15:08:41

That does make more sense (cents? :) ).

View and rule still apply, tho. Better performance still?

create temporary table new_money (product text, cents int4);

create view v_new_money as select product, (cents::numeric/100)::numeric(10,2)
as dollars from new_money;

create rule v_new_money_upd as on update to v_new_money do instead update
new_money set product=new.product, cents=new.dollars * 100 where product =
old.product;

create rule v_new_money_del as on delete to v_new_money do instead delete from
new_money where product = old.product;

create rule v_new_money_ins as on insert to v_new_money do instead insert into
new_money (product, cents) values (new.product, new.dollars * 100);

insert into new_money (product, cents) values ('Flowbee','1995');
insert into new_money (product, cents) values ('Country Hits','995');
insert into v_new_money (product, dollars) values ('ThighMaster','39.95');
update v_new_money set dollars = '14.95' where product='Flowbee';

select * from v_new_money;
   product    | dollars
--------------+---------
 Country Hits |    9.95
 ThighMaster  |   39.95
 Flowbee      |   14.95
(3 rows)

select * from new_money;
   product    | cents
--------------+-------
 Country Hits |   995
 ThighMaster  |  3995
 Flowbee      |  1495
(3 rows)

CG

--- Matt Clark <matt@ymogen.net> wrote:
> > .. I can't _quite_ tell if you're serious or not ... :)
> >
> > If you are serious, are you saying to do something like:
> >
> > CREATE TABLE new_money (product text, dollars int4, cents int4);
>
> Ha :-)  That would not be serious.  I'm pretty sure he meant to just store
> the product cost in cents instead of dollars, e.g.
>
> > CREATE TABLE new_money (product text, cents int4);
> > INSERT INTO new_money (product, cents) values ('Flowbee','1995');
> > INSERT INTO new_money (product, cents) values ('Garth Brooks\'s
> > Greatest Hits','999');
>
> M
>


__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free web site building tool. Try it!
http://webhosting.yahoo.com/ps/sb/

Re: Alternative to "Money" ...

From

Harald Fuchs

Date:

03 February 2004, 15:22:08

In article <20040203160813.81708.qmail@web13808.mail.yahoo.com>,
Chris Gamache <cgg007@yahoo.com> writes:

> That does make more sense (cents? :) ).

> View and rule still apply, tho. Better performance still?

> create temporary table new_money (product text, cents int4);

> create view v_new_money as select product, (cents::numeric/100)::numeric(10,2)
> as dollars from new_money;

> create rule v_new_money_upd as on update to v_new_money do instead update
> new_money set product=new.product, cents=new.dollars * 100 where product =
> old.product;

> create rule v_new_money_del as on delete to v_new_money do instead delete from
> new_money where product = old.product;

> create rule v_new_money_ins as on insert to v_new_money do instead insert into
> new_money (product, cents) values (new.product, new.dollars * 100);

> insert into new_money (product, cents) values ('Flowbee','1995');
> insert into new_money (product, cents) values ('Country Hits','995');
> insert into v_new_money (product, dollars) values ('ThighMaster','39.95');
> update v_new_money set dollars = '14.95' where product='Flowbee';

From where would you get this '14.95'?  From your application?  If
yes, what type is it?  A string or a float?  A string would be
inefficient (like SQLs NUMERIC), a float would not be exact (like SQLs
FLOAT).

Re: Alternative to "Money" ...

From

Chris Gamache

Date:

03 February 2004, 16:03:20

Its coming through as a string. It has to be changed from a string sometime...
I suppose I could put the alteration intelligence into the script that inserts
the information into the DB. That would require rewriting a lot of
application-side code. Besides, I'd rather have as much heavy lifing done with
the (more robust) database as possible. That'll leave the weaker client able to
handle its tasks better. Is this logic flawed? It can't be any worse than it is
right now. I have my own casts for money->int2,int4,int8,float,float4,float8 so
that the mathmatic operators, functions, and aggregates will operate properly.

CG

--- Harald Fuchs <hf118@protecting.net> wrote:
> In article <20040203160813.81708.qmail@web13808.mail.yahoo.com>,
> Chris Gamache <cgg007@yahoo.com> writes:
>
> > That does make more sense (cents? :) ).
>
> > View and rule still apply, tho. Better performance still?
>
> > create temporary table new_money (product text, cents int4);
>
> > create view v_new_money as select product,
> (cents::numeric/100)::numeric(10,2)
> > as dollars from new_money;
>
> > create rule v_new_money_upd as on update to v_new_money do instead update
> > new_money set product=new.product, cents=new.dollars * 100 where product =
> > old.product;
>
> > create rule v_new_money_del as on delete to v_new_money do instead delete
> from
> > new_money where product = old.product;
>
> > create rule v_new_money_ins as on insert to v_new_money do instead insert
> into
> > new_money (product, cents) values (new.product, new.dollars * 100);
>
> > insert into new_money (product, cents) values ('Flowbee','1995');
> > insert into new_money (product, cents) values ('Country Hits','995');
> > insert into v_new_money (product, dollars) values ('ThighMaster','39.95');
> > update v_new_money set dollars = '14.95' where product='Flowbee';
>
> From where would you get this '14.95'?  From your application?  If
> yes, what type is it?  A string or a float?  A string would be
> inefficient (like SQLs NUMERIC), a float would not be exact (like SQLs
> FLOAT).
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org

__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free web site building tool. Try it!
http://webhosting.yahoo.com/ps/sb/

Re: Alternative to "Money" ...

From

Harald Fuchs

Date:

03 February 2004, 17:55:31

In article <20040203160813.81708.qmail@web13808.mail.yahoo.com>,
Chris Gamache <cgg007@yahoo.com> writes:

> View and rule still apply, tho. Better performance still?

> create temporary table new_money (product text, cents int4);

> create view v_new_money as select product, (cents::numeric/100)::numeric(10,2)
> as dollars from new_money;

> create rule v_new_money_upd as on update to v_new_money do instead update
> new_money set product=new.product, cents=new.dollars * 100 where product =
> old.product;

> create rule v_new_money_del as on delete to v_new_money do instead delete from
> new_money where product = old.product;

> create rule v_new_money_ins as on insert to v_new_money do instead insert into
> new_money (product, cents) values (new.product, new.dollars * 100);

> insert into new_money (product, cents) values ('Flowbee','1995');
> insert into new_money (product, cents) values ('Country Hits','995');
> insert into v_new_money (product, dollars) values ('ThighMaster','39.95');
> update v_new_money set dollars = '14.95' where product='Flowbee';

From where would you get this '14.95'?  From your application?  If
yes, what type is it?  A string or a float?  A string would be
inefficient (like SQLs NUMERIC), a float would not be exact (like SQLs
FLOAT).