pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Francis Solomon <francis@stellison.co.uk>
To: mdavis@sevainc.com
To: pgsql-sql@postgresql.org
To: pgsql-interfaces@postgresql.org
To: pgsql-general@postgresql.org
Subject: RE: [SQL] Numeric and money
Date: Wed, 3 Jan 2001 19:00:54 -0000
Message-ID: <NEBBIFFPELJMCJAODNPKCEIJCEAA.francis@stellison.co.uk> (raw)
In-Reply-To: <01C07575.F26395D0.mdavis@sevainc.com>

Hi Michael,

>        create table tst (id int, amount numeric(9,2));
>        insert into tst values (1, 1.10);
>        insert into tst values (2, 1.00);
>        insert into tst values (2, 2.00);
>        select * from tst where amount = 1; -- works
>        select * from tst where amount = 1.1; -- fails
>        select * from tst where amount = 1.10; -- fails

You could try:
SELECT * FROM tst WHERE amount=1.1::numeric;

>        select amount::varchar from tst;  -- fails

This is a bit ugly, but it works:
SELECT ltrim(to_char(amount, '9999999D99')) FROM tst;

>        select amount::money from tst; -- fails

I'm not quite sure why you need to do this. 'amount' is already
'numeric(9,2)' which is as close as you get to 'money'. If you want to
get the result into a var of type 'Currency' in your VB/VBA code (inside
Access), can't you just CCur() the field?

>        select id || ', ' || id from tst;  -- works
>        select id || ', ' || amount from tst; -- fails

Again, a bit ugly, but ...
SELECT id || ', ' || ltrim(to_char(amount, '9999999D99')) FROM tst;

>
> >From within Access, I can't update any table with a numeric
> data type
> because of the "select * from tst where amount = 1.1;"
> failure.  These
> limitations have caused me to wonder what other PostgreSQL
> users are using
> for their money values?  Is numeric(9,2) the best choice for
> money?  I

I am using numeric(9,2) for all my "money" values with VB6 and it works
fine. I use a wrapper function that I wrote to "fix up" arguments so
that postgres plays nicely with them. I tend to manipulate recordset
values with VB/VBA's conversion functions after they're returned, like
CCur() as mentioned above. I'm willing to share my wrappers if you'd
like them.

Hope this helps

Francis Solomon




view thread (5+ messages)  latest in thread

Message-ID: <NEBBIFFPELJMCJAODNPKCEIJCEAA.francis@stellison.co.uk>
Permalink:  ../NEBBIFFPELJMCJAODNPKCEIJCEAA.francis@stellison.co.uk/
Also on:    postgresql.org/message-id/NEBBIFFPELJMCJAODNPKCEIJCEAA.francis@stellison.co.uk

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: francis@stellison.co.uk, mdavis@sevainc.com, pgsql-interfaces@postgresql.org, pgsql-general@postgresql.org
  Subject: Re: [SQL] Numeric and money
  In-Reply-To: <NEBBIFFPELJMCJAODNPKCEIJCEAA.francis@stellison.co.uk>
  References: <01C07575.F26395D0.mdavis@sevainc.com> <NEBBIFFPELJMCJAODNPKCEIJCEAA.francis@stellison.co.uk>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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