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