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

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.

If you'd prefer to substitute something else, for either a.qty or b.qty
individually or the complete sum, see the COALESCE function.
"COALESCE(foo, bar)" means "if foo is not null then foo else bar".
(No, it's not a well-chosen function name ... we can blame the SQL
spec for this too ...)

            regards, tom lane