andras.vaczi@zalando.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
