RE: [SQL] Numeric and money

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