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

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

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

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

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

Solution 2

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

Solution 3

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

Solution 4

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

View more solutions