31

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
Community
  • 1
  • 1
Blanen
  • 682
  • 1
  • 8
  • 21
  • 3
    It's `IS NULL`. – jarlh Jan 26 '17 at 13:29
  • @jarlh "ERROR: cannot cast type boolean to bigint" if I do that. – Blanen Jan 26 '17 at 13:31
  • 1
    Seems like you're (trying) to use some Postgresql specific SQL here (that I don't know.) – jarlh Jan 26 '17 at 13:34
  • 1
    @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. –  Jan 26 '17 at 13:49

5 Answers5

62

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)
);
Aaron
  • 6,988
  • 4
  • 31
  • 48
Vic Colborn
  • 1,895
  • 1
  • 17
  • 21
  • 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 Colborn 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
  • 1
    Read comments above yours. Editing an answer to add your opinion is generally poor form. – Vic Colborn Jun 15 '20 at 16:18
  • Note: `!=` is the alias of `<>` which is the SQL standard notation. – Rafs Mar 08 '23 at 13:18
27

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

cwallenpoole
  • 79,954
  • 26
  • 128
  • 166
7

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

Stefanov.sm
  • 11,215
  • 2
  • 21
  • 21
2

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
Pct Mtnxt
  • 57
  • 3
0

Similar to using num_nonnulls(a, b, c, ...) = 1 as indicated by a_horse_with_no_name before, you can also use arrays to this end with an arbitrary number of columns.

Example:

CONSTRAINT chk_whatever CHECK (cardinality(
    array_remove(
        array[a, b, c, d, e, f, g], NULL
    )
) = 1)

The logic:

  1. Put all the column values in an array
  2. Remove all NULLs from that array
  3. Check that only one value is left.

Tested on Postgres 11.

Rafs
  • 614
  • 8
  • 19