From: Hannu Krosing <hannu@trust.ee>
To: Jason Earl <jearl@box100.com>
Cc: matthew@venux.net
Cc: pgsql-interfaces@postgreSQL.org
Subject: Re: [INTERFACES] Problems with money fields.
Date: Wed, 19 May 1999 11:33:20 +0300
Message-ID: <37427750.14E41957@trust.ee> (raw)
References: <4.1.19990518203016.00ae1d00@mail.venux.net>
<199905190321.VAA28844@earlj.nesusa.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-interfaces@postgresql.org
Cc: hannu@trust.ee, jearl@box100.com, matthew@venux.net
Subject: Re: [INTERFACES] Problems with money fields.
In-Reply-To: <37427750.14E41957@trust.ee>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox