CODEWITHSUNDEEP
sqlpostgresqlpostgresql-8.4
zerkms
zerkms

Reputation: 255005

Tricky logical XOR for nullable values

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?

Upvotes: 12

Views: 3866

Answers (2)

Hugo Tavares
Hugo Tavares

Reputation: 314

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

Upvotes: 8

mu is too short
mu is too short

Reputation: 434775

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

a is null != b is null

Upvotes: 28

Related Questions