Received: from mail.londavia.com ([207.3.150.150]) by mail.postgresql.org (8.11.1/8.11.1) with SMTP id f03IeQ710378 for ; Wed, 3 Jan 2001 13:40:26 -0500 (EST) (envelope-from gkelley@londavia.com) Received: (qmail 8779 invoked from network); 3 Jan 2001 13:39:12 -0500 Received: from unknown (HELO greg) (192.168.1.53) by 192.168.1.2 with SMTP; 3 Jan 2001 13:39:12 -0500 Message-ID: <011401c075b4$d3580ce0$3501a8c0@londavia.com> From: "Greg Kelley" To: Cc: References: <01C07575.F26395D0.mdavis@sevainc.com> Subject: Re: Numeric and money Date: Wed, 3 Jan 2001 13:41:46 -0500 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4522.1200 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200 X-AntiVirus: scanned for viruses by AMaViS 0.2.1 (http://amavis.org/) X-Archive-Number: 200101/18 X-Sequence-Number: 388 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 > > >