pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Alternative to "Money" ...
10+ messages / 6 participants
[nested] [flat]

* Alternative to "Money" ...
@ 2004-01-30 18:55 Chris Gamache <cgg007@yahoo.com>
  2004-01-30 20:31 ` Re: Alternative to "Money" ... Bruno Wolff III <bruno@wolff.to>
  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 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   ` Re: Alternative to "Money" ... Russell Shaw <rjshaw@iprimus.com.au>
  2004-02-02 16:50   ` Re: Alternative to "Money" ... Harald Fuchs <hf118@protecting.net>
  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-30 18:55 Alternative to "Money" ... Chris Gamache <cgg007@yahoo.com>
  2004-01-30 20:31 ` Re: Alternative to "Money" ... Bruno Wolff III <bruno@wolff.to>
@ 2004-01-31 01:39   ` Russell Shaw <rjshaw@iprimus.com.au>
  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-01-30 18:55 Alternative to "Money" ... Chris Gamache <cgg007@yahoo.com>
  2004-01-30 20:31 ` Re: Alternative to "Money" ... Bruno Wolff III <bruno@wolff.to>
@ 2004-02-02 16:50   ` Harald Fuchs <hf118@protecting.net>
  2004-02-03 14:17     ` Re: Alternative to "Money" ... Chris Gamache <cgg007@yahoo.com>
  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-01-30 18:55 Alternative to "Money" ... Chris Gamache <cgg007@yahoo.com>
  2004-01-30 20:31 ` Re: Alternative to "Money" ... Bruno Wolff III <bruno@wolff.to>
  2004-02-02 16:50   ` Re: Alternative to "Money" ... Harald Fuchs <hf118@protecting.net>
@ 2004-02-03 14:17     ` Chris Gamache <cgg007@yahoo.com>
  2004-02-03 14:42       ` Re: Alternative to "Money" ... Matt Clark <matt@ymogen.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-01-30 18:55 Alternative to "Money" ... Chris Gamache <cgg007@yahoo.com>
  2004-01-30 20:31 ` Re: Alternative to "Money" ... Bruno Wolff III <bruno@wolff.to>
  2004-02-02 16:50   ` Re: Alternative to "Money" ... Harald Fuchs <hf118@protecting.net>
  2004-02-03 14:17     ` Re: Alternative to "Money" ... Chris Gamache <cgg007@yahoo.com>
@ 2004-02-03 14:42       ` Matt Clark <matt@ymogen.net>
  2004-02-03 16:08         ` Re: Alternative to "Money" ... 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-01-30 18:55 Alternative to "Money" ... Chris Gamache <cgg007@yahoo.com>
  2004-01-30 20:31 ` Re: Alternative to "Money" ... Bruno Wolff III <bruno@wolff.to>
  2004-02-02 16:50   ` Re: Alternative to "Money" ... Harald Fuchs <hf118@protecting.net>
  2004-02-03 14:17     ` Re: Alternative to "Money" ... Chris Gamache <cgg007@yahoo.com>
  2004-02-03 14:42       ` Re: Alternative to "Money" ... Matt Clark <matt@ymogen.net>
@ 2004-02-03 16:08         ` Chris Gamache <cgg007@yahoo.com>
  2004-02-03 16:17           ` Re: Alternative to "Money" ... Harald Fuchs <hf99@protecting.net>
  2004-02-03 16:21           ` Re: Alternative to "Money" ... Harald Fuchs <hf118@protecting.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-01-30 18:55 Alternative to "Money" ... Chris Gamache <cgg007@yahoo.com>
  2004-01-30 20:31 ` Re: Alternative to "Money" ... Bruno Wolff III <bruno@wolff.to>
  2004-02-02 16:50   ` Re: Alternative to "Money" ... Harald Fuchs <hf118@protecting.net>
  2004-02-03 14:17     ` Re: Alternative to "Money" ... Chris Gamache <cgg007@yahoo.com>
  2004-02-03 14:42       ` Re: Alternative to "Money" ... Matt Clark <matt@ymogen.net>
  2004-02-03 16:08         ` Re: Alternative to "Money" ... Chris Gamache <cgg007@yahoo.com>
@ 2004-02-03 16:17           ` Harald Fuchs <hf99@protecting.net>
  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-01-30 18:55 Alternative to "Money" ... Chris Gamache <cgg007@yahoo.com>
  2004-01-30 20:31 ` Re: Alternative to "Money" ... Bruno Wolff III <bruno@wolff.to>
  2004-02-02 16:50   ` Re: Alternative to "Money" ... Harald Fuchs <hf118@protecting.net>
  2004-02-03 14:17     ` Re: Alternative to "Money" ... Chris Gamache <cgg007@yahoo.com>
  2004-02-03 14:42       ` Re: Alternative to "Money" ... Matt Clark <matt@ymogen.net>
  2004-02-03 16:08         ` Re: Alternative to "Money" ... Chris Gamache <cgg007@yahoo.com>
@ 2004-02-03 16:21           ` Harald Fuchs <hf118@protecting.net>
  2004-02-03 17:00             ` Re: Alternative to "Money" ... 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-01-30 18:55 Alternative to "Money" ... Chris Gamache <cgg007@yahoo.com>
  2004-01-30 20:31 ` Re: Alternative to "Money" ... Bruno Wolff III <bruno@wolff.to>
  2004-02-02 16:50   ` Re: Alternative to "Money" ... Harald Fuchs <hf118@protecting.net>
  2004-02-03 14:17     ` Re: Alternative to "Money" ... Chris Gamache <cgg007@yahoo.com>
  2004-02-03 14:42       ` Re: Alternative to "Money" ... Matt Clark <matt@ymogen.net>
  2004-02-03 16:08         ` Re: Alternative to "Money" ... Chris Gamache <cgg007@yahoo.com>
  2004-02-03 16:21           ` Re: Alternative to "Money" ... Harald Fuchs <hf118@protecting.net>
@ 2004-02-03 17:00             ` Chris Gamache <cgg007@yahoo.com>
  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