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

Question

Is it possible to make a XOR CHECK CONSTRAINT?

I'm doing it on a test table I just made that is called test and has 3 columns:

id, bigint
a, bigint
b, bigint

I made a check constraint for this:

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

Which apparently would work in MSSQL

I tested it by doing this:

INSERT INTO public.test(
    id, a, b)
    VALUES (1, 1, 1);

Which should fail, seeing as it doesn't evaluate to TRUE on either side of the OR. However, it's inserting just fine.

When I look at what postgres actually stored as constraint I get this:

(a IS NOT NULL AND b = NULL::bigint OR b IS NOT NULL AND a = NULL::bigint)

I heard AND takes precedent over OR so even this should still work.

Does anyone have a solution for this? Preferably one that is also possible with three or more columns? I understand that those might be more complicated though.

EDIT: Changing

= NULL

to

IS NULL

give me:

ERROR:  cannot cast type boolean to bigint

@jarlh "ERROR: cannot cast type boolean to bigint" if I do that. — Blanen, Jan 26 '17 at 13:31
Seems like you're (trying) to use some Postgresql specific SQL here (that I don't know.) — jarlh, Jan 26 '17 at 13:34
@Blanen: You are saying `a IS NOT NULL` is allowed and `a IS NULL` throws an error? That is not likely. Please show your complete check constraint — Thorsten Kettner, Jan 26 '17 at 13:36
@ThorstenKettner Yes, you are right. Just pgAdmin4 was being weird I guess. — Blanen, Jan 26 '17 at 13:43
This wouldn't work in SQL Server either if you have the default setting of `ANSI_NULLS = on`. But `ANSI_NULLS = off` is deprecated in SQL Server and won't be available in future versions. — a_horse_with_no_name, Jan 26 '17 at 13:49

score 40 · Answer 1 · edited Jun 11 '20 at 12:48

40

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

edited Jun 11 '20 at 12:48

Aaron

6,808
4
29
47

answered Feb 01 '17 at 19:00

Vic

1,565
1
14
20

Shouldn't your multi-column example use `IS NOT NULL` instead of `IS NULL`? If A `IS NULL` (1) and B `IS NULL` (1), but C `IS NOT NULL` (a valid XOR condition), then the resultant sum would be 2 instead of 1 and fail the check. – OozeMeister Feb 14 '20 at 06:25
Well, that depends on how the constraint is defined. XOR isn't well defined for more than 2 inputs, but "exactly 1 is true" is a common literal translation. As defined here, the check expects exactly one and only one NULL. A variant check for exactly one and only one is NOT NULL is left to the reader as an exercise, as are all of the "no more/less than" variants. – Vic Feb 15 '20 at 15:12
As written, this example checks "exactly 1 is NULL", whereas the more likely desired check is "exactly 1 is NOT NULL" - as is the case for a 3-or-more-way XOR operation. – Aaron Jun 11 '20 at 12:47
Read comments above yours. Editing an answer to add your opinion is generally poor form. – Vic Jun 15 '20 at 16:18

score 21 · Accepted Answer · edited May 27 '22 at 17:30

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

-- works
INSERT INTO xor_test(id, a, b) VALUES (1, null, 1);

-- works
INSERT INTO xor_test(id, a, b) VALUES (2, 1, null);

-- fails
INSERT 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

Stefanov.sm · Answer 3 · 2020-05-20T06:38:27.743

5

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

edited May 20 '20 at 06:38

answered May 19 '20 at 15:28

Stefanov.sm

7,272
2
16
17

P. Qualis · Answer 4 · 2020-08-11T16:43:44.957

Thanks to Vic. I've a similar test in a vue. A least 2 or more columns must be not null, in a left join.

SELECT
    (tbl1.col1 IS NOT NULL)::INTEGER +
    (tbl2.col1 IS NOT NULL)::INTEGER +
    (tbl3.col1 IS NOT NULL)::INTEGER +
    (tbl4.col1 IS NOT NULL)::INTEGER +
    (tbl5.col1 IS NOT NULL)::INTEGER +
    (tbl6.col1 IS NOT NULL)::INTEGER > 1 AS
    b_mult_cols
FROM tlb1
    LEFT JOIN tbl2 ON tlb1.col1 = tlb2.col1
    LEFT JOIN tbl3 ON tlb1.col1 = tlb3.col1
    LEFT JOIN tbl4 ON tlb1.col1 = tlb4.col1
    LEFT JOIN tbl5 ON tlb1.col1 = tlb5.col1
    LEFT JOIN tbl6 ON tlb1.col1 = tlb6.col1

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

4 Answers4