Tricky logical XOR for nullable values

Question

I have 2 nullable CHAR columns and I need to check if only one of them is null.

Doing

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

is boring. And I would like to avoid creating custom functions for that.

I was thinking about something like

COALESCE(a, 1) + COALESCE(b, 1) = 1

but as long as a is char - it causes operand type error.

So, any tricky solutions?

Answer 1

If you mean exactly one is NULL (which matches your existing logic), then:

a is null != b is null

Answer 2

And if you're using PostgreSQL don't forget the parentheses...

ALTER TABLE "schema"."table" ADD CHECK ((key1 IS NULL) <> (key2 IS NULL));

(I spent almost 10 minutes trying to understand what was wrong with my CHECK.)