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

是的,a = NULL并且b = NULL位是@a_horse_with_no_name指示的问题.您也可以考虑这种衍生物,它不需要OR操作员:

create table test 
(
  id integer primary key, 
  a integer, 
  b integer, 
  check ((a IS NULL) != (b IS NULL))
);

当然,这仅适用于两列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)
);

你不能比较NULL值=，你需要IS NULL

(a IS NOT NULL AND b is NULL) OR (b IS NOT NULL AND a is NULL)

对于检查约束，您需要将整个表达式括在括号中：

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