Re: Alternative to "Money" ...

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/

On Fri, Jan 30, 2004 at 10:55:13 -0800,
Chris Gamache <cgg007(at)yahoo(dot)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.

Bruno Wolff III wrote:
> On Fri, Jan 30, 2004 at 10:55:13 -0800,
> Chris Gamache <cgg007(at)yahoo(dot)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

In article <20040130203122(dot)GA1488(at)wolff(dot)to>,
Bruno Wolff III <bruno(at)wolff(dot)to> writes:

> On Fri, Jan 30, 2004 at 10:55:13 -0800,
> Chris Gamache <cgg007(at)yahoo(dot)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.

... 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(at)protecting(dot)net> wrote:
> In article <20040130203122(dot)GA1488(at)wolff(dot)to>,
> Bruno Wolff III <bruno(at)wolff(dot)to> writes:
>
> > On Fri, Jan 30, 2004 at 10:55:13 -0800,
> > Chris Gamache <cgg007(at)yahoo(dot)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/

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

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(at)ymogen(dot)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/

In article <20040203160813(dot)81708(dot)qmail(at)web13808(dot)mail(dot)yahoo(dot)com>,
Chris Gamache <cgg007(at)yahoo(dot)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).

In article <20040203160813(dot)81708(dot)qmail(at)web13808(dot)mail(dot)yahoo(dot)com>,
Chris Gamache <cgg007(at)yahoo(dot)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).

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(at)protecting(dot)net> wrote:
> In article <20040203160813(dot)81708(dot)qmail(at)web13808(dot)mail(dot)yahoo(dot)com>,
> Chris Gamache <cgg007(at)yahoo(dot)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/