[解決済み] Postgres SQL Exclusive OR (XOR) CHECK CONSTRAINT, is it possible?

質問

XOR CHECK CONSTRAINTを作ることは可能ですか？

という名前のテストテーブルでやっています。 テスト で、3つのカラムがあります。

• id, ビッグインテグリティ
• a, bigint
• b, bigint

そのためのチェック制約を作りました。

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

MSSQLではどうやら動作しそうです

こんな感じでテストしてみました。

INSERT INTO public.test(
    id, a, b)
    VALUES (1, 1, 1);

ORの両側でTRUEと評価されないので、失敗するはずです。 しかし、うまく挿入されています。

実際にpostgresが制約として保存しているものを見てみると、このようになります。

(a IS NOT NULL AND b = NULL::bigint OR b IS NOT NULL AND a = NULL::bigint)

ORよりANDが優先されるそうなので、これでも大丈夫なはずです。

どなたか解決策をお持ちの方はいらっしゃいますか？できれば、3列以上でも可能なものがいいのですが。しかし、それらはより複雑になる可能性があることは理解しています。

編集：変更

= NULL

になります。

IS NULL

ください。

ERROR:  cannot cast type boolean to bigint

解決方法は？

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

あるいは、チェック制約を次のように簡略化することもできます。

check ( num_nonnulls(a,b) = 1 )

その分、カラム数が増えても調整しやすくなっています