pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedAlternative to "Money" ...
10+ messages / 6 participants
[nested] [flat]
* Alternative to "Money" ...
@ 2004-01-30 18:55 Chris Gamache <cgg007@yahoo.com>
0 siblings, 1 reply; 10+ messages in thread
From: Chris Gamache @ 2004-01-30 18:55 UTC (permalink / raw)
To: pgsql-admin
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/
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Alternative to "Money" ...
@ 2004-01-30 20:31 Bruno Wolff III <bruno@wolff.to>
parent: Chris Gamache <cgg007@yahoo.com>
0 siblings, 2 replies; 10+ messages in thread
From: Bruno Wolff III @ 2004-01-30 20:31 UTC (permalink / raw)
To: Chris Gamache <cgg007@yahoo.com>; +Cc: pgsql-admin
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Alternative to "Money" ...
@ 2004-01-31 01:39 Russell Shaw <rjshaw@iprimus.com.au>
parent: Bruno Wolff III <bruno@wolff.to>
1 sibling, 0 replies; 10+ messages in thread
From: Russell Shaw @ 2004-01-31 01:39 UTC (permalink / raw)
To: ; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Alternative to "Money" ...
@ 2004-02-02 16:50 Harald Fuchs <hf118@protecting.net>
parent: Bruno Wolff III <bruno@wolff.to>
1 sibling, 1 reply; 10+ messages in thread
From: Harald Fuchs @ 2004-02-02 16:50 UTC (permalink / raw)
To: pgsql-admin
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Alternative to "Money" ...
@ 2004-02-03 14:17 Chris Gamache <cgg007@yahoo.com>
parent: Harald Fuchs <hf118@protecting.net>
0 siblings, 1 reply; 10+ messages in thread
From: Chris Gamache @ 2004-02-03 14:17 UTC (permalink / raw)
To: Harald Fuchs <hf118@protecting.net>; pgsql-admin
... 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/
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Alternative to "Money" ...
@ 2004-02-03 14:42 Matt Clark <matt@ymogen.net>
parent: Chris Gamache <cgg007@yahoo.com>
0 siblings, 1 reply; 10+ messages in thread
From: Matt Clark @ 2004-02-03 14:42 UTC (permalink / raw)
To: cgg007@yahoo.com; Harald Fuchs <hf118@protecting.net>; pgsql-admin
> .. 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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Alternative to "Money" ...
@ 2004-02-03 16:08 Chris Gamache <cgg007@yahoo.com>
parent: Matt Clark <matt@ymogen.net>
0 siblings, 2 replies; 10+ messages in thread
From: Chris Gamache @ 2004-02-03 16:08 UTC (permalink / raw)
To: Matt Clark <matt@ymogen.net>; Harald Fuchs <hf118@protecting.net>; pgsql-admin
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/
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Alternative to "Money" ...
@ 2004-02-03 16:17 Harald Fuchs <hf99@protecting.net>
parent: Chris Gamache <cgg007@yahoo.com>
1 sibling, 0 replies; 10+ messages in thread
From: Harald Fuchs @ 2004-02-03 16:17 UTC (permalink / raw)
To: pgsql-admin
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';
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Alternative to "Money" ...
@ 2004-02-03 16:21 Harald Fuchs <hf118@protecting.net>
parent: Chris Gamache <cgg007@yahoo.com>
1 sibling, 1 reply; 10+ messages in thread
From: Harald Fuchs @ 2004-02-03 16:21 UTC (permalink / raw)
To: pgsql-admin
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';
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Alternative to "Money" ...
@ 2004-02-03 17:00 Chris Gamache <cgg007@yahoo.com>
parent: Harald Fuchs <hf118@protecting.net>
0 siblings, 0 replies; 10+ messages in thread
From: Chris Gamache @ 2004-02-03 17:00 UTC (permalink / raw)
To: Harald Fuchs <hf118@protecting.net>; pgsql-admin
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/
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2004-02-03 17:00 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-01-30 18:55 Alternative to "Money" ... Chris Gamache <cgg007@yahoo.com>
2004-01-30 20:31 ` Bruno Wolff III <bruno@wolff.to>
2004-01-31 01:39 ` Russell Shaw <rjshaw@iprimus.com.au>
2004-02-02 16:50 ` Harald Fuchs <hf118@protecting.net>
2004-02-03 14:17 ` Chris Gamache <cgg007@yahoo.com>
2004-02-03 14:42 ` Matt Clark <matt@ymogen.net>
2004-02-03 16:08 ` Chris Gamache <cgg007@yahoo.com>
2004-02-03 16:17 ` Harald Fuchs <hf99@protecting.net>
2004-02-03 16:21 ` Harald Fuchs <hf118@protecting.net>
2004-02-03 17:00 ` Chris Gamache <cgg007@yahoo.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox