Horton
Menu

Thread

  1. Numeric and money

    Michael J Davis <mdavis@sevainc.com> — 2001-01-03T18:11:36Z

    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

  2. Re: Numeric and money

    Greg Kelley <gkelley@londavia.com> — 2001-01-03T18:41:46Z

    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
>
>
>

  3. RE: [SQL] Numeric and money

    Francis Solomon <francis@stellison.co.uk> — 2001-01-03T19:00:54Z

    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

  4. Re: [SQL] Numeric and money

    Tom Lane <tgl@sss.pgh.pa.us> — 2001-01-03T20:36:49Z

    [ 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

  5. Re: Numeric and money

    Karel Zak <zakkr@zf.jcu.cz> — 2001-01-04T07:03:45Z

    
 > 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

  6. IPC Shared Memory

    Katsuyuki Tanaka <katsut@cse.unsw.edu.au> — 2001-02-18T04:27:47Z

    	Hi when i run postmaster i got the following error and
	postmaser doesn't start,

	FATAL 1:  InitProcGlobal: IpcSemaphoreCreate failed
	IpcSemaphoreCreate: semget failed (No space left on device) key=8888014,
	num=16, permission=600
	This type of error is usually caused by an improper
	shared memory or System V IPC semaphore configuration.
	For more information, see the FAQ and platform-specific
	FAQ's in the source directory pgsql/doc or on our

	
	i made query to admin and the max shared mem setting is already
	set to the max possible

	*
	* IPC Shared Memory
	*
	4294967295      max shared memory segment size (SHMMAX)
     		1  min shared memory segment size (SHMMIN)
   		100  shared memory identifiers (SHMMNI)
    		10  max attached shm segments per process (SHMSEG)

	
	I tried -N and -B but didn't have luck. Could anyone help me on
	this? This problem appeared after admin changed their config 
	i believe, could that be it?

	Thanks
	Katsu