<rikard(dot)pavelic(at)zg(dot)htnet(dot)hr> wrote:
> --This doesn't work as expected
> select * from bad where c is not null;
Are you seeing any behavior which does not match the documentation
and the standard?
http://www.postgresql.org/docs/current/interactive/functions-comparison.html
says:
| Note: If the expression is row-valued, then IS NULL is true when
| the row expression itself is null or when all the row's fields are
| null, while IS NOT NULL is true when the row expression itself is
| non-null and all the row's fields are non-null. Because of this
| behavior, IS NULL and IS NOT NULL do not always return inverse
| results for row-valued expressions, i.e., a row-valued expression
| that contains both NULL and non-null values will return false for
| both tests. This definition conforms to the SQL standard, and is a
| change from the inconsistent behavior exhibited by PostgreSQL
| versions prior to 8.2.
When using a NULL test with a row-value, it can help to imagine the
word "ENTIRELY" right after the word IS. The above query will only
return a row from "bad" if the row value "c" IS [ENTIRELY] NOT NULL
-- in other words, any NULL in the row causes it to be excluded.
Moving the NOT in front of the IS results in a test for rows from
"bad" where it is NOT true that row value "c" IS [ENTIRELY] NULL.
That works for me, anyway. Some find the rules around NULL
illogical and argue for just memorizing them as a set of facts
rather than trying to make sense of them.
-Kevin