PostgreSQLでは通常、一意な列に複数のSQL NULL値を格納できます。予期しない重複キーエラーを診断するには、該当するインデックスを特定し、実際に送信された値を調べ、定義によってNULLの比較方法が変わるかを確認してください。一意インデックス

この手順は、Macでpsqlまたはnode-postgresを使って記事を挿入する際の問題をデバッグする人を対象としています。架空の記事行を使い、デフォルトの動作を再現し、失敗の原因となるルールを追跡してから、適切な修正方法を選ぶ診断手順を示します。

NULLを許容する一意列では、実際には何が許可されるのか？

NULLを許容する列にはNULLを格納できます。一意制約は、どの組み合わせの重複を許すかを決めます。通常のUNIQUE (slug)では、NULL以外のスラッグの重複は拒否されますが、NULLの重複は許可されます。通常のUNIQUE (section, slug)では、制約対象のいずれかの列がNULLなら、その制約のもとでは他の列が同じ組み合わせの重複も許可されます。テーブル上の別の一意ルールは引き続き適用されます。制約

PostgreSQL 15では、NULLを一意性チェックで等しい値として扱うNULLS NOT DISTINCTが導入されました。複合キーでは、組み合わせ全体が一致する必要があります。('notes', NULL)は別の('notes', NULL)と競合しますが、('guides', NULL)とは競合しません。PostgreSQL 14はこの句をサポートしていません。リリース15、一意インデックス

実際のルールまたは入力 値の重複によって重複キーエラーが発生するか
通常のUNIQUE (slug)とSQLのNULL このルール単独では発生しない
UNIQUE NULLS NOT DISTINCT (slug) PostgreSQL 15以降では発生する
通常のUNIQUE (slug)と'' 発生する。これは空文字列
通常のUNIQUE (slug)と'NULL' 発生する。これはテキスト
同じ行にある別の一意キー NULLを許容するスラッグとは別に発生する

SQLのNULL、空文字列、テキスト'NULL'はそれぞれ異なる入力です。PostgreSQLの比較に関するドキュメントでは、NULLと通常のテキスト値は明確に区別されています。NULLかどうかを調べるにはIS NULLを使います。比較関数と演算子

Macのターミナルでデフォルトの動作を再現する

既存のPostgreSQL接続と、Terminalで使えるpsqlコマンドが必要です。普段使っている接続設定でセッションを開いてください。たとえば、ローカルデータベース名がpostgresの場合は、次を実行します。-dオプションでデータベースを選択します。psql

psql -d postgres

バージョン固有の構文を選ぶ前に、接続先サーバーのバージョンを確認してください。server_versionはサーバーのバージョンを返します。NULLS NOT DISTINCTを使えるかどうかは、このバージョンが基準になります。プリセットオプション

SHOW server_version;

新しいセッションで、このベースラインの例を最初から最後まで実行してください。通常の一意制約はPostgreSQL 14以降で動作します。この例では、デモ用の一時テーブルだけを作成します。

CREATE TEMP TABLE article_probe (
    article_id integer PRIMARY KEY,
    title text NOT NULL,
    slug text,
    CONSTRAINT article_probe_slug_key UNIQUE (slug)
);

INSERT INTO article_probe (article_id, title, slug)
VALUES
    (1, 'Paper shapes', NULL),
    (2, 'Color arrangements', NULL),
    (3, 'Desk sketches', ''),
    (4, 'Pattern notes', 'NULL');

SELECT article_id, title, slug,
       slug IS NULL AS is_sql_null,
       slug = '' AS is_empty_string
FROM article_probe
ORDER BY article_id;

表示された定義では、4つのINSERTはすべて成功するはずです。最初の2行はis_sql_null = trueで、3行目はis_empty_string = true、4行目にはリテラルのテキストが入っています。NULLを含む比較はそれ自体がNULLを返す場合があるため、結果を解釈するには明示的なNULL判定列を使ってください。制約、比較関数と演算子

このベースラインは、問題の切り分けに役立ちます。この例は成功するのにアプリケーションで失敗する場合は、アプリケーションの実際のテーブル定義と送信値をベースラインと比較してください。列がNULLを許容するというだけでは、失敗の原因となるルールは特定できません。

エラーの原因となるルールを特定する

まず、PostgreSQLがunique_violationを示すコードであるSQLSTATE 23505を確認してください。エラーメッセージの文言と照合するのではなく、コードを確認します。次に、報告されたスキーマ、テーブル、制約名、および取得できる場合は詳細を記録します。インデックスをCREATE UNIQUE INDEXで直接作成した場合でも、PostgreSQLはエラーの制約名フィールドにおいてインデックスを制約として扱います。PostgreSQLエラーコード、エラーおよび通知メッセージのフィールド

ベースラインと同じpsqlセッションで、次の診断クエリを使ってインデックスを調べます。pg_indexesビューには、各インデックスのスキーマ、名前、再構成された定義が表示されます。クエリを調整する際は、エラーで報告されたテーブルとスキーマの両方を条件に指定してください。pg_indexes

SELECT schemaname, tablename, indexname, indexdef
FROM pg_indexes
WHERE tablename = 'article_probe'
ORDER BY schemaname, indexname;

エラーに記載された名前から確認し、一意な定義をすべて読み取ってください。重複したarticle_idは、slugがNULLでも主キー違反になります。主キーには一意でNULL以外の値が必要です。行内の別の列がNULLを許容していても、この要件は緩和されません。制約

ベースラインとの違いとして、次の3点を確認してください。

  • NULLの等価性： NULLS NOT DISTINCTは、NULLキーの重複が競合するよう意図的に設定します。
  • 式： 一意性の対象が、保存された列ではなく計算された値の場合があります。
  • 述語： 部分インデックスのルールは、WHERE条件を満たす行にのみ適用されます。

式インデックスの場合は、競合を調べる際にインデックス式を再現してください。たとえば、lower(slug)を使うインデックスは、大文字と小文字だけが異なるNULL以外のスラッグを2つ拒否できます。COALESCE(slug, '')を使うインデックスは、NULLと空文字列の両方を同じインデックス値に変換するため、これらの入力は競合する可能性があります。これは、式インデックスの一意性と、COALESCEの文書化された動作によるものです。式に対するインデックス、条件式

部分一意インデックスも注意深く読み取る必要があります。WHERE slug IS NULLを付けてsectionに作成したインデックスは、スラッグがNULLの行の中で、セクションごとに該当行を1行だけ許可します。slug自体の一意性を保証するものではありません。PostgreSQLのドキュメントでは、部分一意インデックスは述語で選ばれたサブセット内の一意性を保証すると説明されています。部分インデックス

node-postgresが実際に送信する値を確認する

node-postgresは、JavaScriptのnullとundefinedのクエリパラメーターを、どちらもSQLのNULLに変換します。空文字列と文字列'NULL'は、通常のテキスト入力のままです。また、NULLのパラメーターを指定することと、SQLで列を省略することを区別してください。省略した列には、宣言されたデフォルト値が設定されます。デフォルトがなければNULLが設定されます。クエリ、INSERT

次の完全なプログラムを使うと、これらの違いを確認できます。Node.js、npm、および既存のPostgreSQL接続設定が必要です。作業用フォルダーで依存関係をインストールし、プログラムをdiagnose-null.mjsとして保存してから実行してください。クライアントは標準のPostgreSQL接続用環境変数を使います。接続

npm install pg

import pg from 'pg';

const { Client } = pg;
const client = new Client();

async function main() {
  await client.connect();
  try {
    await client.query(`
      CREATE TEMP TABLE article_node_probe (
        title text NOT NULL,
        slug text DEFAULT 'untitled',
        CONSTRAINT article_node_probe_slug_key UNIQUE (slug)
      )
    `);

    const samples = [
      { title: 'Paper shapes', slug: null },
      { title: 'Color arrangements', slug: undefined },
      { title: 'Desk sketches', slug: '' },
      { title: 'More sketches', slug: '' },
      { title: 'Pattern notes', slug: 'NULL' },
      { title: 'More patterns', slug: 'NULL' },
    ];

    for (const sample of samples) {
      console.log('Input:', {
        title: sample.title,
        type: typeof sample.slug,
        value: sample.slug,
      });

      try {
        const result = await client.query(
          `INSERT INTO article_node_probe (title, slug)
           VALUES ($1, $2)
           RETURNING title, slug, slug IS NULL AS is_sql_null`,
          [sample.title, sample.slug],
        );
        console.log('Inserted:', result.rows[0]);
      } catch (error) {
        if (error.code !== '23505') throw error;
        console.log('Duplicate:', {
          code: error.code,
          schema: error.schema,
          table: error.table,
          constraint: error.constraint,
          detail: error.detail,
        });
      }
    }
  } finally {
    await client.end();
  }
}

main().catch((error) => {
  console.error(error);
  process.exitCode = 1;
});

PGDATABASE=postgres node diagnose-null.mjs

このプログラムのスキーマでは、宣言されたデフォルト値があっても、最初の2行はSQLのNULLとして保存されるはずです。最初の空文字列と最初の'NULL'文字列のINSERTは成功し、それらを繰り返すと23505が発生するはずです。プログラムは想定される競合を報告し、それ以外のエラーは再スローします。診断で使うプロパティ名は、node-postgresのDatabaseErrorフィールドに対応しています。クエリ、DatabaseErrorのソース

データベースの例は、取得したドキュメントと照らし合わせて確認しましたが、準備中にPostgreSQLに接続して実行したものではありません。

NULL可能キーを、該当する行を見落とさずに検索する

slug = NULLを使った診断クエリでは、NULLのスラッグは見つかりません。NULLを固定で検索するにはIS NULLを使い、値がNULLかNULL以外かのどちらにもなり得る場合はIS NOT DISTINCT FROMを使います。後者は比較時に2つのNULLを等しい値として扱います。比較関数と演算子

この診断クエリは、同じセッション内の前述のarticle_probeテーブルに対して実行します。NULLのスラッグを持つ2行が返されるはずです。

SELECT article_id, title, slug
FROM article_probe
WHERE slug IS NOT DISTINCT FROM NULL::text
ORDER BY article_id;

パラメーター化された検索で使う場合、対応する述語部分はWHERE slug IS NOT DISTINCT FROM $1::textです。複合ルールではすべてのキー構成要素を照合し、インデックス式や部分インデックスの述語があればそれも再現してください。一致するNULL行を見つければ、何が保存されているかは確認できます。一意性に違反するかどうかは、インデックス定義によって決まります。

意図したルールに合う修正を選ぶ

記事のスラッグがいくつでも欠けてよい場合は、通常のUNIQUE (slug)ですでに要件を表現できます。誤ってテキスト値が入力されている場合、意図しない式が使われている場合、またはエラーで特定された別のキーが重複している場合は、それを修正してください。NULLの扱いを変えても、別のキーで発生した競合は解消されません。

セクションごとにNULLのスラッグを1つだけ許可する要件なら、PostgreSQL 15以降ではそれを直接表現できます。この独立したデモでは、最初の行がすでにそのセクションとNULLの組み合わせを使用しているため、最後のINSERTは23505を起こすことを想定しています。リリース15、一意インデックス

CREATE TEMP TABLE article_one_missing (
    section text NOT NULL,
    slug text,
    title text NOT NULL,
    CONSTRAINT article_one_missing_key
        UNIQUE NULLS NOT DISTINCT (section, slug)
);

INSERT INTO article_one_missing (section, slug, title)
VALUES ('notes', NULL, 'Paper shapes');

-- Expected: 23505 on article_one_missing_key.
INSERT INTO article_one_missing (section, slug, title)
VALUES ('notes', NULL, 'Color arrangements');

PostgreSQL 14では、この2列の設計に部分一意インデックスを使います。次の独立した例は、sectionがNULL以外で、slugが唯一NULLを許容するキー構成要素であるため、同じルールを実装できます。これは、PostgreSQLの部分インデックスに関する文書化された動作に基づく構成です。部分インデックス

CREATE TEMP TABLE article_legacy (
    section text NOT NULL,
    slug text,
    title text NOT NULL
);

CREATE UNIQUE INDEX article_legacy_present_key
ON article_legacy (section, slug)
WHERE slug IS NOT NULL;

CREATE UNIQUE INDEX article_legacy_missing_key
ON article_legacy (section)
WHERE slug IS NULL;

INSERT INTO article_legacy (section, slug, title)
VALUES ('notes', NULL, 'Paper shapes');

-- Expected: 23505 on article_legacy_missing_key.
INSERT INTO article_legacy (section, slug, title)
VALUES ('notes', NULL, 'Color arrangements');

最初のインデックスは、セクション内で値が入力されたスラッグの重複を防ぎます。2つ目は、セクションごとにスラッグが欠けた行を1行だけ許可します。NULLを許容するキー構成要素がほかにもある場合、その可能な組み合わせをさらに検討する必要があります。この2つのインデックスによる構成は、記載されたスキーマに固有です。

最後に、競合時の処理を明示的に選んでください。ON CONFLICT DO NOTHINGは競合するINSERTをスキップしますが、元の値を修正するものではありません。ON CONFLICT DO UPDATEでは競合対象の指定が必要です。その列、式、適用される述語によって、意図したルールを特定できなければなりません。どちらの処理も、通常の一意性がNULLの重複を許している状況で、NULL同士の競合を発生させるものではありません。INSERT

報告されたインデックス名をその定義に結び付け、送信されたキー値を再現し、そのルールのもとでなぜ競合するのかを説明できれば、診断は完了です。修正後にまったく同じ入力をもう一度試し、返された結果と保存された行の両方を確認して、意図した記事が処理されたことを確かめてください。

参考資料