Skip site navigation (1) Skip section navigation (2)

Re: [INTERFACES] Problems with money fields.

From: Hannu Krosing <hannu(at)trust(dot)ee>
To: Jason Earl <jearl(at)box100(dot)com>
Cc: matthew(at)venux(dot)net, pgsql-interfaces(at)postgreSQL(dot)org
Subject: Re: [INTERFACES] Problems with money fields.
Date: 1999-05-19 08:33:20
Message-ID: 37427750.14E41957@trust.ee (view raw or flat)
Thread:
Lists: pgsql-interfaces
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

In response to

pgsql-interfaces by date

Next:From: Stuart RisonDate: 1999-05-19 10:58:18
Subject: Installing pl/pgsql under Irix 5.3
Previous:From: Jason EarlDate: 1999-05-19 03:21:40
Subject: Re: [INTERFACES] Problems with money fields.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group