Postgres SQL Exclusive OR (XOR) CHECK CONSTRAINT, is it possible?

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));

You can't compare NULL values with =, you need IS NULL

(a IS NOT NULL AND b is NULL) OR (b IS NOT NULL AND a is NULL)

For a check constraint you need to enclose the whole expression in parentheses:

create table xor_test (  id integer primary key,   a integer,   b integer,   check ((a IS NOT NULL AND b is NULL) OR (b IS NOT NULL AND a is NULL)));-- worksINSERT INTO xor_test(id, a, b) VALUES (1, null, 1);-- worksINSERT INTO xor_test(id, a, b) VALUES (2, 1, null);-- failseINSERT INTO xor_test(id, a, b) VALUES (3, 1, 1); 

Alternatively the check constraint can be simplified to

check ( num_nonnulls(a,b) = 1 )

That's also easier to adjust to more columns

This is clear Exclusive-OR. Why not define it as a boolean operator first? It might be useful for other cases too.

CREATE OR REPLACE FUNCTION public.xor (a boolean, b boolean) returns boolean immutable language sql AS$$SELECT (a and not b) or (b and not a);$$;CREATE OPERATOR # (    PROCEDURE = public.xor,     LEFTARG = boolean,     RIGHTARG = boolean);

Then CHECK ((a IS NULL) # (b IS NULL))