PostgreSQLのEXCLUDED擬似テーブルは、ON CONFLICT操作中に挿入が提案された行を表します。歴史的に、MySQLやOracle環境から移行した開発者は、直接の等価比較（=）がアップサートパターン内の値の変化を検出するのに十分だと考えることが多いです。しかし、SQL標準の三値論理では、NULLは未知の状態を示し、NULL = NULLはNULL（未知）に評価され、TRUEにはならないことが示されています。
これは、衝突解決クローズが、WHERE EXCLUDED.phone != users.phoneのようなWHERE句を追加することで更新を最適化しようとするときに重大な問題を引き起こします。既存の行と提案された行の両方が電話番号のカラムにNULLを持つ場合、この比較はNULLを返し、WHERE述語が失敗します。結果として、データベースは、ビジネスロジックの文脈では実際に異なる価値を持つかもしれないのに、更新をスキップしてしまうか、または新しいデータのNULLと古いデータのNULLの区別ができません。
解決策は、IS DISTINCT FROM演算子を利用することで、これによりNULLは比較可能な値として扱われます。WHERE EXCLUDED.column IS DISTINCT FROM table.columnのように更新句を構造化することで、両方の値がNULLの場合、比較はFALSEを返し（変化なしを示す）、一方がNULLで他方がそうでないときはTRUEを返します。これにより、決定論的な動作が確保され、不要な書き込みが防止されます。
INSERT INTO patient_records (clinic_id, external_id, phone, updated_at) VALUES (101, 'P-2024-001', NULL, NOW()) ON CONFLICT (clinic_id, external_id) DO UPDATE SET phone = COALESCE(EXCLUDED.phone, patient_records.phone), updated_at = EXCLUDED.updated_at WHERE EXCLUDED.phone IS DISTINCT FROM patient_records.phone;
ある病院ネットワークは、50の外部クリニックからの毎日の患者受け入れデータを中央のPostgreSQLデータウェアハウスに同期する必要がありました。各クリニックは患者の電話番号が欠けている場合、空の文字列として出力されたCSVファイルをエクスポートしました。COPYコマンドは、取り込み中にそれをNULLに変換しました。既存のPython ETLスクリプトは、SQLAlchemyを使用して、ON CONFLICT (clinic_id, external_id) DO UPDATE SET phone = EXCLUDED.phoneでバルクアップサートを実行しました。
問題は、クリニックの職員が、中央システムに直接入力された有効な電話番号が夜間の同期後に神秘的に消えたと報告したときに発生しました。調査の結果、外部フィードがNULL（未知の電話を示す）を送信したときに、条件付きで実行されるSET句によって既存の有効な番号が上書きされることが分かりました。単純なフィルタWHERE EXCLUDED.phone != patient_records.phoneを追加したが、両方がNULLの場合、比較はNULL（未知）を返し、更新が誤ってスキップされ、また新しい値がNULLで古いものがそうでない場合、異なるPostgreSQLのマイナーバージョン間で論理が一貫性を欠く動作になることがわかりました。
三つの解決策が評価されました。
最初のアプローチは、SET句でCOALESCEを専ら使用しました：SET phone = COALESCE(EXCLUDED.phone, patient_records.phone)。これにより、NULLでの上書きを防げましたが、すべての衝突で更新を強制し、phoneカラムの高価なB-Treeインデックスの再構築を引き起こし、「no-op」変更を正当な修正としてログに記録する監査トリガーを発火させました。これにより、WAL（Write-Ahead Log）トラフィックが300％増加し、複製遅延が脅かされ、ディスクI/Oが飽和しました。
第二の解決策は、NULLを扱うために明示的なブールロジックを試みました：WHERE (EXCLUDED.phone != patient_records.phone) OR (EXCLUDED.phone IS NULL AND patient_records.phone IS NOT NULL)。論理的には正しいが、この冗長なパターンは15のnullableカラムにわたって慎重なメンテナンスを必要とし、クエリオプティマイザーを混乱させました。プランナーは、インデックススキャンを放棄して20百万行のテーブルで逐次スキャンを選択し、ETLジョブが6時間のメンテナンスウィンドウを超えました。
第三の解決策は、WHERE句内のすべてのnullableカラムにIS DISTINCT FROMを実装しました。これにより、真实なデータ変更を正確に特定する簡潔でsargableな述語が提供され、必要な場合のみ更新を許可し、冗長なトリガーの実行とWALの生成を排除し、一貫したクエリプランを維持しました。
チームは、重要な連絡先フィールドには第3の解決策を、パフォーマンスよりも上書き保護が重要な非重要メタデータには第1の解決策を採用しました。その結果は劇的でした：同期ジョブの所要時間は45分から12分に短縮され、複製遅延は5秒以下で安定し、「消える電話番号」の事件は展開の最初の週に完全に停止しました。
なぜWHERE EXCLUDED.column != table.columnが両方の値がNULLのときに行をスキップするのか、またそれがPostgreSQLの更新メカニズムとどのように相互作用するのか？
多くの候補者は、二つのNULLが等しくない場合、比較はTRUEを返し、更新が許可されるべきだと考えがちです。しかし、SQLは三値論理を使用します：NULLは未知の値を示します。NULLとのいかなる比較（NULL = NULLまたはNULL != NULLを含む）はNULL（未知）を返し、ブール値のTRUEまたはFALSEにはなりません。PostgreSQLのWHERE句では、TRUEと評価される行のみが進行します；NULLはFALSEとして扱われます。したがって、二つのNULLの電話番号を比較すると、結果はNULLとなり、更新はスキップされ、システムは変更が不要だと誤って考えます。IS DISTINCT FROMは、NULL対NULLの場合はFALSEを返し、正しく同一であると示し、適切な場合にのみ更新をスキップする一方、一方の値がNULLで他方がそうでない場合はTRUEを返します。
マルチカラム一意制約におけるカラムの順序は、ON CONFLICT解決の性能にどのように影響を与え、衝突ターゲットがインデックス定義と正確に一致しない場合はどうなりますか？
候補者はしばしば、PostgreSQLが衝突ターゲット（ON CONFLICT (...)にリストされたカラム）が、カラムの順序と任意の関数式を含む一意インデックス定義と正確に一致する必要があることを見落とします。(clinic_id, external_id)に一意インデックスが存在する場合でも、クエリがON CONFLICT (external_id, clinic_id)を指定すると、プランナーはインデックスを推論できず、「ON CONFLICT仕様に一致する一意または排除制約がありません」というエラーを投げることになります。たとえ推論に成功しても、カラムの順序が一致しないと、オプティマイザーがインデックスのみのスキャンを使用できず、ヒープフェッチを強制し、I/Oコストが大幅に増加します。
SET句におけるCOALESCE(EXCLUDED.column, table.column)の使用と、特にトリガーの実行と行のバージョニングに関して、WHERE EXCLUDED.column IS DISTINCT FROM table.columnを使用することの違いは何ですか？
SET句でのCOALESCEの使用は、行に値を書き込みます（新しいデータまたは保持された古いデータのいずれか）。この操作は新しい行バージョン（CTID）を生成し、WALへの書き込みを行い、テーブルに関連付けられたすべてのBEFOREおよびAFTERトリガーを発火させます。最終的な値が前の状態と同一であっても、これが発生します。これは、監査テーブルに"ノーオペ"のノイズを生み出し、複製負荷を増加させます。一方、IS DISTINCT FROMを使用したWHERE句は、実際に変更がない場合は全く行の修正を防ぎます。新しいタプルバージョンは作成されず、トリガーが発火せず、WALの生成を回避します。この違いは、監査ロギングや外部キーのカスケードがある高スループットシステムにとって重要です。