Right, the a = NULL
and b = NULL
bit was the issue as @a_horse_with_no_name indicated. You might also consider this derivative, which doesn’t require the OR
operator:
create table test
(
id integer primary key,
a integer,
b integer,
check ((a IS NULL) != (b IS NULL))
);
Of course that works exclusively with only two column XOR
comparison. With three or more column XOR
comparison in a similar test table you could resort to a similar approach more like this:
create table test
(
id integer primary key,
a integer,
b integer,
c integer,
check ((a IS NOT NULL)::INTEGER +
(b IS NOT NULL)::INTEGER +
(c IS NOT NULL)::INTEGER = 1)
);