Sender: hannu@kodu.home.ee
Message-ID: <37427750.14E41957@trust.ee>
Date: Wed, 19 May 1999 11:33:20 +0300
From: Hannu Krosing <hannu@trust.ee>
Organization: Trust-O-Matic =?iso-8859-1?Q?O=DC?=
MIME-Version: 1.0
To: Jason Earl <jearl@box100.com>
CC: matthew@venux.net, pgsql-interfaces@postgreSQL.org
Subject: Re: [INTERFACES] Problems with money fields.
References: <4.1.19990518203016.00ae1d00@mail.venux.net>
	<199905190321.VAA28844@earlj.nesusa.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Jason Earl wrote:
> 
> Apparently a NULL money value is not equal to zero.  That is probably
> a good thing.
> 
> The select you want for selecting all of the null values is:
> 
> SELECT * FROM test WHERE total IS NULL;
> 
>    Hello,
> 
>    I was wondering if someone could shed some light on money fileds for me.  I
>    am having a problem in that a money field plus a null money field equals
>    null.  Try this:
> 
>    create table test(total money);
>    insert into test values('');
>    insert into test valuse(NULL);
> 
>    select * from test;
>    total
>    -----
>    $0.00
> 
>    (2 rows)
> 
>    select '1'::money + total from test;
>    ?column?
>    --------
>    $1.00
> 
>    (2 rows)
> 
>    Why is the second column blank?  This is really throwing my calculations
>    because there are times when $0.00 is different from no value at all.

Yes, that's the point - you can't add NULL and 0

Oracle has a NVL(FIELD,VALUE_FOR_NULL) function, that allows you to 
special-case NULL-s, and I _think_ ver.6.5 will have something similar

>    Also, how can I get a query of all rows where the money value is NULL?
>    This fails:
> 
>    select * from test where total = NULL;

try SELECT * FROM TEST WHERE TOTAL IS NULL

>  If I can't query for a money field with a NULL value, why can I insert one?

You can always forbid yourself installing NULLs

create table test(
   mymoney money not null
);

or even have a default value of 0 for the fields

create table test(
   mymoney money default 0
);

or both :)

create table test(
   mymoney money not null default 0
);

------------------
Hannu