Re: + operator with a possible NULL operand - Mailing list pgsql-novice

From Michael Glaesemann
Subject Re: + operator with a possible NULL operand
Date
Msg-id E6F41673-91B9-11D7-BEFD-0005029FC1A7@myrealbox.com
Whole thread Raw
In response to Re: + operator with a possible NULL operand  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-novice
On Thursday, May 29, 2003, at 15:38 Asia/Tokyo, Tom Lane wrote:

> Michael Glaesemann <grzm@myrealbox.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


pgsql-novice by date:

Previous
From: Michael Glaesemann
Date:
Subject: Re: + operator with a possible NULL operand
Next
From: "M. Bastin"
Date:
Subject: MD5 salt