CODEWITHSUNDEEP
sqlpostgresqlconstraintsxor
Blanen
Blanen

Reputation: 782

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

Upvotes: 40

Views: 38072

Answers (5)

Rafs
Rafs

Reputation: 812

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.

Upvotes: 2

user330315
user330315

Reputation:

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

Upvotes: 32

Pct Mtnxt
Pct Mtnxt

Reputation: 65

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

Upvotes: 2

Vic Colborn
Vic Colborn

Reputation: 2065

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

Upvotes: 71

Stefanov.sm
Stefanov.sm

Reputation: 13049

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

Upvotes: 8

Related Questions