Re: + operator with a possible NULL operand

On Thursday, May 29, 2003, at 15:38 Asia/Tokyo, Tom Lane wrote:

> Michael Glaesemann <grzm(at)myrealbox(dot)com> writes:
>> SELECT a.qty AS aa,
>> b.qty AS bb,
>> a.qty + b.qty AS cc
>> FROM ...
>
>> The select statement seems to work just fine, except that where a.qty
>> or b.qty are NULL, cc is also NULL.
>
> Yup, that's how it should be according to the SQL spec. NULL is
> effectively "unknown", so the result of adding it to anything else
> is also unknown, ie NULL.

Tom (and Vincent), thanks for the confirmation of what I suspected.
It's definitely good to know. And so fast! I get up to go to lunch,
come back, and there's my answer. Wow. I'm just hoping someday I'll
know enough to be able to contribute a bit back.

> If you'd prefer to substitute something else, for either a.qty or b.qty
> individually or the complete sum, see the COALESCE function.

After a brief tour through the PostgreSQL documention (COALESCE isn't
in the index) and subsequent modifications to my query, everything is
golden! Thanks so much.

I'm suspecting I'm going to use COALESCE (arg, 0) quite a bit. Ripe for
a custom function or operator, I'm thinking. :)

Thanks again!

Michael Glaesemann
grzm myrealbox com