pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedProblems with money fields.
3+ messages / 3 participants
[nested] [flat]
* Problems with money fields.
@ 1999-05-19 00:39 Matthew Hagerty <matthew@venux.net>
1999-05-19 03:21 ` Re: [INTERFACES] Problems with money fields. Jason Earl <jearl@box100.com>
0 siblings, 1 reply; 3+ messages in thread
From: Matthew Hagerty @ 1999-05-19 00:39 UTC (permalink / raw)
To: pgsql-interfaces
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.
Also, how can I get a query of all rows where the money value is NULL?
This fails:
select * from test where total = NULL;
ERROR: parser: parse error at or near "null"
and this gives me the rows with $0.00:
select * from test where total = '';
total
-----
$0.00
(1 row)
If I can't query for a money field with a NULL value, why can I insert one?
Thank you,
Matthew Hagerty
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [INTERFACES] Problems with money fields.
1999-05-19 00:39 Problems with money fields. Matthew Hagerty <matthew@venux.net>
@ 1999-05-19 03:21 ` Jason Earl <jearl@box100.com>
1999-05-19 08:33 ` Re: [INTERFACES] Problems with money fields. Hannu Krosing <hannu@trust.ee>
0 siblings, 1 reply; 3+ messages in thread
From: Jason Earl @ 1999-05-19 03:21 UTC (permalink / raw)
To: matthew@venux.net; +Cc: pgsql-interfaces
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.
Also, how can I get a query of all rows where the money value is NULL?
This fails:
select * from test where total = NULL;
ERROR: parser: parse error at or near "null"
and this gives me the rows with $0.00:
select * from test where total = '';
total
-----
$0.00
(1 row)
If I can't query for a money field with a NULL value, why can I insert one?
Thank you,
Matthew Hagerty
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [INTERFACES] Problems with money fields.
1999-05-19 00:39 Problems with money fields. Matthew Hagerty <matthew@venux.net>
1999-05-19 03:21 ` Re: [INTERFACES] Problems with money fields. Jason Earl <jearl@box100.com>
@ 1999-05-19 08:33 ` Hannu Krosing <hannu@trust.ee>
0 siblings, 0 replies; 3+ messages in thread
From: Hannu Krosing @ 1999-05-19 08:33 UTC (permalink / raw)
To: Jason Earl <jearl@box100.com>; +Cc: matthew@venux.net; 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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~1999-05-19 08:33 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-05-19 00:39 Problems with money fields. Matthew Hagerty <matthew@venux.net>
1999-05-19 03:21 ` Jason Earl <jearl@box100.com>
1999-05-19 08:33 ` Hannu Krosing <hannu@trust.ee>
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