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
The PostgreSQL Bitwise OR ( | ) operator compares corresponding bits for each operand and produces a 1 when either or both bits are 1 otherwise it returns 0. The PostgreSQL Bitwise XOR ( # ) operator is similar to OR operator.
Exclusion constraints ensure that if any two rows are compared on the specified columns or expressions using the specified operators, at least one of these operator comparisons will return false or null. The syntax is: CREATE TABLE circles ( c circle, EXCLUDE USING gist (c WITH &&) ); See also CREATE TABLE ...
To find the name of a constraint in PostgreSQL, use the view pg_constraint in the pg_catalog schema. Join the view pg_catalog. pg_constraint with the view pg_class ( JOIN pg_class t ON t. oid = c.
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)
);
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
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))
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With