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

According to the ANSI SQL-92 specification:

        " 1) If the value of any <numeric primary> simply contained in a
            <numeric value expression> is the null value, then the result of
            the <numeric value expression> is the null value."

Here is a link to the link for the ANSI SQL-92 specification:

http://luuk.xs4all.nl/sql/sqlref.htm#sql92

My understanding is that any intra-row functions in which one of the
operators is a null will yield a null. Something that is confusing is that
inter-row functions may or may not yield a null.

That is

SELECT SUM(numvar) FROM tablex

Will treat any numvar which are null as if they were a zero. However, the
following will not treat it as zero but non-existent.

SELECT COUNT(numvar) FROM table x

Will pretend that any null numvar is non existent and will not count it.

Nulls are not part of relational theory and as far as I know the rules seem
to be to do whatever seems useful for most users. It should be noted that
Codd did try to fit nulls into relational theory using "three valued logic".
I don't think this was widely excepted.

Vincent Hikida,
Member of Technical Staff - Urbana Software, Inc.
"A Personalized Learning Experience"

www.UrbanaSoft.com

----- Original Message -----
From: "Michael Glaesemann" <grzm@myrealbox.com>
To: <pgsql-novice@postgresql.org>
Sent: Wednesday, May 28, 2003 10:58 PM
Subject: [NOVICE] + operator with a possible NULL operand


> I'm getting my feet wet using PostgreSQL and have run in to a bit of a
> snag with using the + operator.
>
> I want to create a table that has a third column that is the sum of the
> values of two other columns. In essence:
>
> SELECT a.qty AS aa,
> b.qty AS bb,
> a.qty + b.qty AS cc
> FROM ...
>
> The FROM is a subquery that includes a number of full joins, so there
> are occasions when a.qty or b.qty are NULL.
>
> The select statement seems to work just fine, except that where a.qty
> or b.qty are NULL, cc is also NULL. (I can see how this might not work,
> PostgreSQL protesting, "Okay, you want me to add this and ... and ...
> and what, exactly?" But maybe I've misconceived what the problem is.)
>
> I've googled for "postgresql mathematical operator null argument",
> taken a gander at the html help files included with the PostgreSQL
> installation and through Practical PostgreSQL in the sections on
> mathematical operators, but didn't find anything that mentions this.
> I'm probably not looking in the right places.
>
> If someone point me in the direction I should look, I'd really
> appreciate it.
>
> Thanks for your time!
>
> Michael Glaesemann
> grzm myrealbox com
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>