pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedNumeric and money
5+ messages / 5 participants
[nested] [flat]
* Numeric and money
@ 2001-01-03 18:11 Michael Davis <mdavis@sevainc.com>
0 siblings, 4 replies; 5+ messages in thread
From: Michael Davis @ 2001-01-03 18:11 UTC (permalink / raw)
To: 'pgsql-sql@postgresql.org' <pgsql-sql@postgresql.org>; 'pgsql-admin@postgresql.org' <pgsql-admin@postgresql.org>; pgsql-interfaces; 'pgsql-general@postgresql.org' <pgsql-general@postgresql.org>
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Numeric and money
@ 2001-01-03 18:41 Greg Kelley <gkelley@londavia.com>
parent: Michael Davis <mdavis@sevainc.com>
3 siblings, 0 replies; 5+ messages in thread
From: Greg Kelley @ 2001-01-03 18:41 UTC (permalink / raw)
To: mdavis@sevainc.com; +Cc: pgsql-admin@postgresql.org
I have set up currency as single precision and it seems to work between
MS-Acces 97 and pgsql v7.
----- Original Message -----
From: "Michael Davis" <mdavis@sevainc.com>
To: <pgsql-sql@postgresql.org>; <pgsql-admin@postgresql.org>;
<pgsql-interfaces@postgresql.org>; <pgsql-general@postgresql.org>
Sent: January 03, 2001 1:11 PM
Subject: [ADMIN] 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
>
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* RE: [SQL] Numeric and money
@ 2001-01-03 19:00 Francis Solomon <francis@stellison.co.uk>
parent: Michael Davis <mdavis@sevainc.com>
3 siblings, 0 replies; 5+ messages in thread
From: Francis Solomon @ 2001-01-03 19:00 UTC (permalink / raw)
To: mdavis@sevainc.com; pgsql-sql@postgresql.org; pgsql-interfaces; pgsql-general@postgresql.org
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [SQL] Numeric and money
@ 2001-01-03 20:36 Tom Lane <tgl@sss.pgh.pa.us>
parent: Michael Davis <mdavis@sevainc.com>
3 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2001-01-03 20:36 UTC (permalink / raw)
To: mdavis@sevainc.com <mdavis@sevainc.com>; +Cc: 'pgsql-sql@postgresql.org' <pgsql-sql@postgresql.org>; 'pgsql-general@postgresql.org' <pgsql-general@postgresql.org>
[ 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Numeric and money
@ 2001-01-04 07:03 Karel Zak <zakkr@zf.jcu.cz>
parent: Michael Davis <mdavis@sevainc.com>
3 siblings, 0 replies; 5+ messages in thread
From: Karel Zak @ 2001-01-04 07:03 UTC (permalink / raw)
To: Michael Davis <mdavis@sevainc.com>; +Cc: 'pgsql-sql@postgresql.org' <pgsql-sql@postgresql.org>
> 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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2001-01-04 07:03 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-01-03 18:11 Numeric and money Michael Davis <mdavis@sevainc.com>
2001-01-03 18:41 ` Greg Kelley <gkelley@londavia.com>
2001-01-03 19:00 ` Francis Solomon <francis@stellison.co.uk>
2001-01-03 20:36 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-01-04 07:03 ` Karel Zak <zakkr@zf.jcu.cz>
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