Re: "IS NOT NULL" != "NOT NULL"

> > test_pgsql=# SELECT COUNT(*) FROM test WHERE col2 NOTNULL;
> >  count
> > -------
> >      3
> > (1 row)
>
> > test_pgsql=# SELECT COUNT(*) FROM test WHERE col2 != NULL;
> >  count
> > -------
> >      0
> > (1 row)
>
> These are not the same thing.  See any of the past discussions about
> why "null = null" and "null != null" and so forth do not do what a
> novice might expect.

http://www.postgresql.org/idocs/index.php?functions-comparison.html

The archives appear to be broken, but the above link seems to explain
things well enough.  I still can't grok the rationale as to why NULL
is interpreted as unknown and not interpreted as empty.  I understand
that you can't compare two values that are unknown until the unknowns
are known.  However why null isn't interpreted as empty is something I
haven't grasped.  '' is different than empty ('' is a defined string
that's 0 characters in length), which is different than null
(unknown).  In my mind: "col2 != NULL" is the same as "col2 IS NOT
NULL", but I fully understand why "col2 = NULL" is an invalid
statement.  Not a biggie, just a source of curiosity.  -sc

--
Sean Chittenden