💽

Postgresql の ON CONFLICT が NULL 値に対してうまく動作しない: COALESCE で解決してみる

に公開
PostgreSQL
SQL
tech

PostgreSQLでNULL値があるときのUPSERT（ON CONFLICT DO UPDATE）を正しく動作させる方法

ゴール

PostgreSQL <= v14 でINSERT INTO ~ ON CONFLICT (col1, col2, col3) ~ DO UPDATE をした際、col1 ~ 3 のどれかに NULL があると CONFLICT していないと判断されて、UPDATE をしてくれませんでした。

そこで COALESCE() 関数を使ってこの問題に対処したので、その方法をご紹介します。

開発環境

  • PostgreSQL 14.x

事前準備

まずは問題が発生するテーブル構造を準備します。

CREATE TABLE test_table (
    id SERIAL PRIMARY KEY,
    col1 INT NOT NULL,
    col2 INT NOT NULL,
    col3 INT,  -- NULL値を含む可能性があるカラム
    col4 INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 通常のユニーク制約
CREATE UNIQUE INDEX idx_test_unique 
ON test_table(col1, col2, col3, col4);

問題の確認

ON CONFLICT DO UPDATE うまくが動作しない問題を確認します。

-- これを2回実行すると行が2つ作成される
INSERT INTO test_table (col1, col2, col3, col4) 
VALUES (1, 2, NULL, 300)
ON CONFLICT (col1, col2, col3, col4) 
DO UPDATE SET created_at = CURRENT_TIMESTAMP;

結果はこのようになります。

col1, col2, col3, col4 の値はすべて同じに見えますが、 NULL が異なる値だと判定されてしまい、2行作成されました。
この挙動をする理由は、PostgreSQLではNULL値は互いに等しくないとして扱われるため、NULL値を含むカラムに対する通常のユニークインデックスではON CONFLICTが正しく動作しないからです。

そこで、 ON CONFLICT の条件に COALESCE() を使って NULL なら番兵（センチネル値）を使って CONFLICT の条件を使うことでこの問題を解決していきます。

※現在のインデックスのまま COALESCE() を使うと以下のようなエラーがでます。

解決方法の実装

インデックスを再作成し、ON CONFLICT の条件の中で COALESCE()関数を使用してNULL値を固定値（センチネル値）に変換して解決します。

インデックスの再作成

既存のインデックスを削除し、COALESCE()を使った新しいインデックスを作成します。

-- 既存のインデックスを削除
DROP INDEX IF EXISTS idx_test_unique;

-- COALESCE()を使った新しいインデックスを作成
CREATE UNIQUE INDEX idx_test_fixed 
ON test_table(col1, col2, COALESCE(col3, -1), col4);

UPSERTの修正

ON CONFLICTでも同じCOALESCE()式を使用します。

INSERT INTO test_table (col1, col2, col3, col4) 
VALUES (1, 2, NULL, 300)
ON CONFLICT (col1, col2, COALESCE(col3, -1), col4) 
DO UPDATE SET created_at = CURRENT_TIMESTAMP;

動作確認

修正後の動作を確認します。

-- 初回挿入（INSERT実行される）
INSERT INTO test_table (col1, col2, col3, col4) 
VALUES (1, 2, NULL, 300)
ON CONFLICT (col1, col2, COALESCE(col3, -1), col4) 
DO UPDATE SET created_at = CURRENT_TIMESTAMP;

-- 同じデータで再実行（UPDATE実行される）
INSERT INTO test_table (col1, col2, col3, col4) 
VALUES (1, 2, NULL, 300)
ON CONFLICT (col1, col2, COALESCE(col3, -1), col4) 
DO UPDATE SET created_at = CURRENT_TIMESTAMP;

以下のように UPSERT が正常に動作することが確認できました。

補足

センチネル値の保護

センチネル値（-1）が実際のデータと重複しないよう、チェック制約を使うのも有効ですね。

ALTER TABLE test_table 
ADD CONSTRAINT chk_col3_not_sentinel 
CHECK (col3 IS NULL OR col3 > 0);

おわりに

PostgreSQLでNULL値を含むカラムでUPSERT（ON CONFLICT DO UPDATE）を使用する際は、COALESCE()関数を使用することで問題を解決できます。
重要なポイントは、インデックス作成時とON CONFLICT使用時で同じCOALESCE式を使うことです。
ただし、確か Postgresql 15 では NULL 値を同じ値として判定するような unique 制約があるみたいなので、これを使うのが良さそうです。

Discussion