Numeric and money

Поиск
Список
Период
Сортировка
Искать

Numeric and money

От:
Michael Davis <mdavis@sevainc.com>
Дата:
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


Re: Numeric and money

От:
"Greg Kelley" <gkelley@londavia.com>
Дата:
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" 
To: ; ;
; 
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
>
>
>

FAQ