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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Stuart Rison | 1999-05-19 10:58:18 | Installing pl/pgsql under Irix 5.3 |
Previous Message | Jason Earl | 1999-05-19 03:21:40 | Re: [INTERFACES] Problems with money fields. |