<rikard.pave...@zg.htnet.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
-- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
