Numeric and money

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?

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

[ 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

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

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