sqlpostgresqlconstraintsxor

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

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:

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

  • 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