Thread: Numeric and money

Hello Everyone,

I am in the process of migrating my Access97 application to PostgreSQL.  So
far everything looks great with one exception.  I converted my currency
fields in Access to numeric(9,2) as recommended in the PostgreSQL
documentation.  Many things to don't play well with the numeric the data
type.  Here are some examples:

       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
       select amount::varchar from tst;  -- fails
       select amount::money from tst; -- fails
       select id || ', ' || id from tst;  -- works
       select id || ', ' || amount from tst; -- fails

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
think that adding numeric to text and text to numeric operators will fix
most of these issues.  I plan to add these operators very soon and thought
I would ask if anyone has done this before and could provide me an example
or two before I start.  Does anyone know of any internal functions that
already exist to convert numeric to text so that I don't have to write one?
 I know that psql successfully does this.

Thanks, Michael Davis
Database Architect and Senior Software Engineer, Seva Inc.
Office:        303-460-7360        Fax: 303-460-7362
Mobile:    720-320-6971
Email:        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

[ mail lists trimmed to something a tad more reasonable ]

Michael Davis <mdavis@sevainc.com> writes:
> 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.

Yeah.  The problem here is the conflict between interpreting '1.1' as
a "float8" constant vs. interpreting it as a "numeric" constant.  In order
to fix this we need to settle on a better type promotion hierarchy among
the various numeric datatypes.  You can find past discussions of the
issue in the pghackers archives.  I made a proposal on 13-May-2000 that
I think was objected to by some people, though I don't recall exactly
why.

            regards, tom lane

> Date: Wed, 3 Jan 2001 11:11:36 -0700> From: Michael Davis <mdavis@sevainc.com>
!> To: "'pgsql-sql@postgresql.org'" <pgsql-sql@postgresql.org>,
!>     "'pgsql-admin@postgresql.org'" <pgsql-admin@postgresql.org>,
!>     "'pgsql-interfaces@postgresql.org'" <pgsql-interfaces@postgresql.org>,
!>     "'pgsql-general@postgresql.org'" <pgsql-general@postgresql.org>> Subject: [SQL] Numeric and money

 Man, where is limit between spam and question to mailing list?!

                Karel