How to set NAND constraint with Postgresql-CodePudding

I have two date columns refused_at and requested_at in a table foo, both dates can be NULL, only one can be filled, both cannot be filled at the same time.

So, I need to add a CHECK CONSTRAINT, what should I use for NAND operator ?

It should probably look like this:

ALTER TABLE foo
  ADD CONSTRAINT check_status
  CHECK ((refused_at IS NULL) <> (requested_at IS NULL));

but <> seems to be an XOR test.

CodePudding user response：

<> is not an "xor" - you just compare two boolean values, e.g. true <> false or true <> true.

You can express this with boolean logic, but using Postgres' num_nonnulls() function makes this a lot easier.

ALTER TABLE foo
  ADD CONSTRAINT check_status
  CHECK (num_nonnulls(refused_at, requested_at) <= 1);

This will allow zero "non nulls" (meaning both are null) or at least one.