From: | Michael Davis <mdavis(at)sevainc(dot)com> |
---|---|
To: | "'pgsql-sql(at)postgresql(dot)org'" <pgsql-sql(at)postgresql(dot)org>, "'pgsql-admin(at)postgresql(dot)org'" <pgsql-admin(at)postgresql(dot)org>, "'pgsql-interfaces(at)postgresql(dot)org'" <pgsql-interfaces(at)postgresql(dot)org>, "'pgsql-general(at)postgresql(dot)org'" <pgsql-general(at)postgresql(dot)org> |
Subject: | Numeric and money |
Date: | 2001-01-03 18:11:36 |
Message-ID: | 01C07575.F26395D0.mdavis@sevainc.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin pgsql-general pgsql-interfaces pgsql-sql |
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(at)sevainc(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Greg Kelley | 2001-01-03 18:41:46 | Re: Numeric and money |
Previous Message | mlw | 2001-01-03 16:43:20 | Re: Removing a constraint? |
From | Date | Subject | |
---|---|---|---|
Next Message | Gary Wesley | 2001-01-03 18:21:30 | Loading optimization |
Previous Message | François LODIER | 2001-01-03 18:09:00 | PQexec problem |
From | Date | Subject | |
---|---|---|---|
Next Message | Lamar Owen | 2001-01-03 18:17:42 | Re: DBD::Pg |
Previous Message | Tom Lane | 2001-01-03 16:45:54 | Re: ODBC - Invalid protocol character |
From | Date | Subject | |
---|---|---|---|
Next Message | Greg Kelley | 2001-01-03 18:41:46 | Re: Numeric and money |
Previous Message | Tom Lane | 2001-01-03 18:09:24 | Re: PL/pgSQL: recursion? |