PostgreSQL 通常允許唯一欄位中有多個 SQL NULL 值。若要診斷非預期的重複鍵錯誤，請找出確切的索引、檢查實際送出的值，並確認其定義是否改變了 NULL 的比較方式。唯一索引

本指南適用於使用 Mac 上的 psql 或 node-postgres，偵錯文章插入作業的人員。指南使用合成的文章資料列，並提供診斷步驟：重現預設行為、追查失敗的規則，然後選擇適當的修正方式。

可為 NULL 的唯一欄位實際允許什麼？

可為 NULL 的欄位可以包含 NULL；唯一性限制會決定哪些組合可以重複。在一般的 UNIQUE (slug) 下，重複的非 NULL slug 會遭到拒絕，而重複的 NULL 則允許。在一般的 UNIQUE (section, slug) 下，受限制的任一欄位為 NULL 時，該限制允許其他相同的組合。資料表上的其他唯一性規則仍然適用。限制條件

PostgreSQL 15 引入了 NULLS NOT DISTINCT，會在唯一性檢查時將 NULL 視為相等。對於複合鍵，必須整個組合相符：('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 的 slug 無關

SQL NULL、空字串和文字 'NULL' 是不同的輸入。PostgreSQL 的比較文件明確區分 NULL 與一般文字值；請使用 IS NULL 測試是否為 NULL。比較函數與運算子

在 Mac Terminal 重現預設行為

你需要現有的 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;

依照顯示的定義，四次插入都應成功。前兩列的 is_sql_null = true 為 NULL；第三列有 is_empty_string = true；第四列則包含字面文字。涉及 NULL 的比較本身可能會回傳 NULL，因此請使用明確的 NULL 判定欄來解讀結果。限制條件、比較函數與運算子

這個基準範例提供了實用的判斷界線。如果應用程式發生失敗，但此範例成功，請比較應用程式實際使用的資料表定義與提交的值，以及基準範例中的內容。僅憑欄位可為 NULL，無法判定是哪條規則造成失敗。

找出錯誤背後的確切規則

請從 SQLSTATE 23505 著手；這是 PostgreSQL 對 unique_violation 使用的代碼。請檢查代碼，而不要比對錯誤訊息的措辭。接著記錄錯誤回報的結構描述、資料表、限制名稱，以及可取得的詳細資訊。即使索引是直接透過 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;

請檢視每個唯一性定義，並先查看錯誤訊息中的名稱。即使 slug 為 NULL，重複的 article_id 仍會違反主鍵。主鍵要求值唯一且不可為 NULL；資料列中其他欄位可為 NULL，並不會放寬這項要求。限制條件

請留意基準範例與實際定義之間的三種差異：

  • NULL 相等性： NULLS NOT DISTINCT 會刻意讓重複的 NULL 鍵互相衝突。
  • 運算式： 唯一性規則可能套用於計算所得的值，而非儲存的欄位。
  • 述詞： 部分索引只會對符合其 WHERE 條件的資料列套用規則。

若是運算式索引，調查衝突時請重現索引運算式。例如，對 lower(slug) 建立的索引，可能會拒絕兩個只有大小寫不同的非 NULL slug。對 COALESCE(slug, '') 建立的索引，會將 NULL 和空字串都映射為相同的索引值；因此，這些輸入可能會發生衝突。這是運算式索引唯一性以及 COALESCE 已文件化行為所導致的結果。運算式索引、條件運算式

部分唯一索引也需要仔細檢視。對 section 建立且附有 WHERE slug IS NULL 的索引，會在 slug 為 NULL 的資料列中，對每個 section 最多允許一筆相符資料列。它不會對 slug 本身強制唯一性。PostgreSQL 文件指出，部分唯一索引會在述詞選取的子集中強制唯一性。部分索引

檢查 node-postgres 實際送出的內容

node-postgres 會將 JavaScript null 和 undefined 查詢參數都轉換為 SQL NULL。空字串和字串 'NULL' 仍是一般文字輸入。另外，請區分提供 NULL 參數與省略 SQL 欄位：PostgreSQL 會將省略的欄位設為其宣告的預設值；若沒有預設值，則設為 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

在此程式的結構描述下，前兩列應儲存 SQL NULL，即使欄位宣告了預設值也一樣。第一個空字串和第一個 'NULL' 字串應能插入；重複插入時應產生 23505。程式會回報這些預期中的衝突，並重新拋出其他錯誤。其診斷屬性名稱與 node-postgres 的 DatabaseError 欄位相符。查詢、DatabaseError 原始碼

準備本文時，已依照取得的文件檢視資料庫範例，但未在 PostgreSQL 上執行。

查詢可為 NULL 的鍵，避免漏掉符合的資料列

使用 slug = NULL 進行診斷查詢時，找不到 slug 為 NULL 的資料列。若要固定搜尋 NULL，請使用 IS NULL；若值可能為 NULL 或非 NULL，請使用 IS NOT DISTINCT FROM。後者會在比較時將兩個 NULL 視為相等。比較函數與運算子

此診斷查詢會在同一工作階段中，對前面的 article_probe 資料表執行。它應回傳兩筆 slug 為 NULL 的資料列。

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 資料列，可以確認目前儲存的內容；這些資料列是否違反唯一性，仍取決於索引定義。

選擇符合預期規則的修正方式

如果文章可以有任意數量的缺漏 slug，一般的 UNIQUE (slug) 已能表達此需求。請修正錯誤的文字值、非預期的運算式，或錯誤訊息指出的其他重複鍵。改變 NULL 的處理方式，無法解決其他鍵上的衝突。

如果需求是每個 section 只能有一個 NULL slug，PostgreSQL 15 或更新版本可以直接表達此規則。以下獨立示範最後刻意執行一筆預期會產生 23505 的插入：第一列已佔用該 section 與 NULL 的組合。版本 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 中，對這種特定的雙欄設計，可以使用部分唯一索引。以下獨立範例實作相同的規則，因為 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');

第一個索引會防止同一 section 中重複出現已填入的 slug。第二個索引則允許每個 section 有一個缺漏的 slug。若鍵中還有其他可為 NULL 的元件，就必須進一步考慮其可能組合；這兩個索引的做法僅適用於上述結構描述。

最後，請明確選擇衝突處理方式。ON CONFLICT DO NOTHING 會略過發生衝突的插入，但不會修正底層值。ON CONFLICT DO UPDATE 需要指定衝突目標，而其中的欄位、運算式及適用的述詞必須能識別預期的規則。這兩種動作都不會在一般唯一性規則允許重複 NULL 時，額外造成 NULL 衝突。INSERT

完成診斷的條件是：你能將錯誤回報的索引名稱連結到其定義、重現提交的鍵值，並依照該規則說明衝突原因。修正後，請再次提交完全相同的輸入，並檢查回傳結果及儲存的資料列，確認預期的文章已妥善處理。

來源