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