Alternative to "Money" ...

[Chris Gamache]

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/

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Bruno Wolff III]

On Fri, Jan 30, 2004 at 10:55:13 -0800,

Post by Chris Gamache
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.

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Russell Shaw]

Post by Bruno Wolff III
On Fri, Jan 30, 2004 at 10:55:13 -0800,

Post by Chris Gamache
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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly

[Harald Fuchs]

Post by Bruno Wolff III
On Fri, Jan 30, 2004 at 10:55:13 -0800,

Post by Chris Gamache
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

[Chris Gamache]

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

Post by Harald Fuchs

Post by Bruno Wolff III
On Fri, Jan 30, 2004 at 10:55:13 -0800,

Post by Chris Gamache
Well, after living with the (depreciated) Money datatype for 5 years, I

think

Post by Bruno Wolff III

Post by Chris Gamache
its finally time to say goodbye (and now that we have DROP COLUMN it'll be

MUCH

Post by Bruno Wolff III

Post by Chris Gamache
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/

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

[Matt Clark]

Post by Chris Gamache
.. I can't _quite_ tell if you're serious or not ... :)
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.

Post by Chris Gamache
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


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

[Chris Gamache]

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

Post by Matt Clark

Post by Chris Gamache
.. I can't _quite_ tell if you're serious or not ... :)
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.

Post by Chris Gamache
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/

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

[Harald Fuchs]

Post by Chris Gamache
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

[Chris Gamache]

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

Post by Chris Gamache

Post by Chris Gamache
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)

Post by Chris Gamache
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

Post by Chris Gamache
new_money where product = old.product;
create rule v_new_money_ins as on insert to v_new_money do instead insert

into

Post by Chris Gamache
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/

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

[Harald Fuchs]

Post by Chris Gamache
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 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match