Re: BUG #8141: multi-column check expression evaluating to NULL

andras(dot)vaczi(at)zalando(dot)de writes:
> Consider the following table with a CHECK constraint:

> CREATE TABLE check_test
> (
> id integer NOT NULL,
> col integer,
> CONSTRAINT unique_with_null_check1 CHECK (col >= 1 AND id < 20)
> );

> This INSERT statement succeeds:

> INSERT INTO check_test (id, col) VALUES (1, NULL);

> While, col being NULL, the whole CHECK condition evaluates to NULL - this is
> covered in the documentation.

> But this is refused:
> INSERT INTO check_test (id, col) VALUES (21, NULL);

> ERROR: new row for relation "check_test" violates check constraint
> "unique_with_null_check1"

> I think this behaviour should be either also mentioned in the docs or
> cosidered a bug.

I see no bug here. In the first case, the "col >= 1" condition yields
NULL while "id < 20" yields TRUE, so you have NULL AND TRUE which is
NULL, which is considered a "pass" for a CHECK condition per spec.
In the second case, "col >= 1" is still NULL, but "id < 20" is FALSE,
so you have NULL AND FALSE which is FALSE (*not* NULL), and so failure
is per spec.

Yes, the behavior of AND/OR with NULLs is documented.
http://www.postgresql.org/docs/9.1/static/functions-logical.html

regards, tom lane