postgresql Postgres SQL Exclusive OR (XOR) CHECK CONSTRAINT，有可能吗？

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

提问by Blanen

Is it possible to make a XOR CHECK CONSTRAINT?

是否可以进行 XOR 检查约束？

I'm doing it on a test table I just made that is called testand has 3 columns:

我在我刚刚制作的一个名为test的测试表上做这件事，它有 3 列：

• id, bigint
• a, bigint
• b, bigint

• ID，大整数
• 一、大整数
• b、大整数

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

这显然适用于 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.

哪个应该失败，因为它在 OR 的任何一侧都没有评估为 TRUE。但是，它插入得很好。

When I look at what postgres actually stored as constraint I get this:

当我查看 postgres 实际存储为约束的内容时，我得到了这个：

(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.

我听说 AND 优先于 OR 所以即使这样也应该仍然有效。

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

采纳答案by a_horse_with_no_name

You can't compare NULL values with =, you need IS NULL

您不能将 NULL 值与 进行比较=，您需要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

这也更容易适应更多列

回答by Vic

Right, the a = NULLand b = NULLbit was the issue as @a_horse_with_no_name indicated. You might also consider this derivative, which doesn't require the ORoperator:

是的，正如@a_horse_with_no_name 指出的那样，a = NULL和b = NULL位是问题。您也可以考虑这个不需要OR运算符的导数：

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 XORcomparison. With three or more column XORcomparison in a similar test table you could resort to a similar approach more like this:

当然，这仅适用于两列XOR比较。XOR在类似的测试表中进行三列或更多列的比较，您可以采用更像这样的类似方法：

create table test 
(
  id integer primary key, 
  a integer, 
  b integer, 
  c integer, 
  check ((a IS NULL)::INTEGER + 
         (b IS NULL)::INTEGER + 
         (c IS NULL)::INTEGER = 1)
);

回答by Stefanov.sm

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

然后检查 ((a IS NULL) # (b IS NULL))