PostgreSQL 無法將 ON CONFLICT DO UPDATE 用作通用的「符合任一唯一鍵」操作。選定一個定義鍵，或在交易中明確檢查兩個鍵，並拒絕會指向兩筆不同資料列的輸入。

本教學協助在 Mac 的 Terminal 中工作的開發者使用 node-postgres 實作第二種策略：兩個鍵都沒有符合項目時插入、恰好一篇文章符合時更新，若兩個鍵分別指向不同文章則明確回報衝突。所有資料列都是合成資料。

SQL 範例以 PostgreSQL 18 為目標，這是擷取到的最新版文件所對應的版本。此實作使用一般、未分割的資料表，並設有兩個立即生效的唯一限制。依照其他版本手冊調整範例前，請先確認伺服器的主要版本。PostgreSQL 18 INSERT 文件

為何兩個唯一限制不能合併成一個衝突目標

假設一篇文章有外部參照和 slug，兩者都必須各自唯一。請在空白的示範資料庫中，以這份完整設定開始；將它存為 setup.sql。

CREATE TABLE articles (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    external_key text NOT NULL,
    slug text NOT NULL,
    title text NOT NULL,
    CONSTRAINT articles_external_key_key UNIQUE (external_key),
    CONSTRAINT articles_slug_key UNIQUE (slug)
);

INSERT INTO articles (external_key, slug, title)
VALUES
    ('feed-101', 'blue-notes', 'Blue notes'),
    ('feed-202', 'green-notes', 'Green notes');

這些宣告會強制執行兩條不同規則。若以 UNIQUE (external_key, slug) 取代它們，就會改變資料模型：只有兩者組成的配對需要唯一，因此不同組合中可以重複外部鍵，也可以重複 slug。讓兩個欄位都維持 NOT NULL，也可避免在此範例中引入可為空鍵值的行為。PostgreSQL 限制

對於 DO UPDATE，PostgreSQL 要求指定衝突目標。欄位清單描述符合項目唯一索引所包含的欄位；它不代表「任一欄位皆可」。因此，ON CONFLICT (external_key, slug) 無法從上述兩個獨立限制推斷仲裁索引。指定限制名稱會選取該限制，而語法只提供一個 ON CONFLICT 子句，不能串接多個替代處理程序。PostgreSQL INSERT：ON CONFLICT 子句

更深層的問題是判斷傳入資料列代表哪一篇現有文章。假設傳入值為 feed-101 和 green-notes：外部鍵屬於藍色文章，而 slug 屬於綠色文章。若以這兩個傳入鍵更新任一資料列，就會與另一筆資料列衝突。選擇第一個符合項目會掩蓋尚未解決的輸入判斷。

決定每種符合情況的處理方式

撰寫查詢前，請先使用下表。這是根據此資料結構提出的應用程式策略，不是 PostgreSQL 自動提供的行為。

傳入外部鍵 傳入 slug 符合的不同資料列 本教學的處理方式
未使用 未使用 0 插入一篇文章
符合某篇文章 未使用 1 更新該文章，包含其 slug
未使用 符合某篇文章 1 更新該文章，包含其外部鍵
符合文章 A 符合文章 A 1 更新文章 A
符合文章 A 符合文章 B 2 拒絕輸入；保留兩篇文章

恰好一筆符合的情況刻意允許任一鍵識別文章，並允許變更另一個鍵。請確認這符合匯入需求。若外部鍵絕不可變更，則只有 slug 符合時應改為應用程式衝突。

如果實際規則較簡單，也就是「外部鍵永遠決定文章」，請使用一般的指定目標 upsert。以下完整敘述式可在上述設定之後執行：

INSERT INTO articles (external_key, slug, title)
VALUES ('feed-101', 'blue-notes', 'Revised blue notes')
ON CONFLICT (external_key) DO UPDATE
SET slug = EXCLUDED.slug,
    title = EXCLUDED.title
RETURNING id, external_key, slug, title;

其中 EXCLUDED 提供擬寫入的值。另一個唯一限制仍會生效：若將擬寫入的 slug 改成 green-notes，會產生獨立的唯一性衝突，而不會改為處理另一篇文章。指定目標 upsert 的原子化插入或更新保證，前提是沒有獨立錯誤發生。PostgreSQL INSERT 文件

讓任一鍵判斷在並行寫入期間保持穩定

先查詢再插入，會在判斷與寫入之間形成空檔。另一個交易可能在這段空檔插入符合的鍵。在查詢中加入 FOR UPDATE 會鎖定查回的資料列；若沒有資料列符合，就沒有查回的資料列可鎖定。因此，單靠資料列鎖定不足以處理這種零符合情況。PostgreSQL 明確鎖定

對於小型匯入或偶爾進行的桌面操作，一個直接的方法是在檢查任一鍵之前取得 SHARE ROW EXCLUSIVE 資料表鎖。這種鎖定模式會與一般插入、更新和刪除所取得的 ROW EXCLUSIVE 鎖衝突，也會與另一個相同模式的鎖衝突。一般讀取仍可相容。PostgreSQL 資料表層級鎖定模式

接著依序執行：開始交易、取得資料表鎖、找出符合的不同資料列、依照決策表處理，然後提交。明確鎖定會等待不相容的鎖，並持續持有到交易結束。PostgreSQL LOCK

此處請明確使用 READ COMMITTED。查詢會看見在該查詢開始前已提交的資料，包括取得鎖時等待期間完成的寫入。將這種快照行為與已取得的資料表鎖結合後，此實作便有穩定依據來判斷並寫入。PostgreSQL 交易隔離

這是範圍較廣的鎖定：無關的文章寫入也會等待。請縮短交易時間，並避免在鎖定到提交之間進行互動提示或外部作業。若匯入頻繁且並行度高，採用此設計前，請重新考慮是否能以一個標準鍵定義身分。

使用 node-postgres 實作交易

你需要一個正在執行的 PostgreSQL 18 示範資料庫、psql 命令，以及目前安裝的 node-postgres 套件所支援的 Node.js 版本。在 Terminal 中安裝套件並載入設定檔。若示範目錄尚未有套件中繼資料，第一個命令會建立它。node-postgres 安裝與相容性

npm init -y
npm install pg
psql -v ON_ERROR_STOP=1 -f setup.sql

這些命令假設一般本機連線設定已選取示範資料庫。程式使用 new Pool()，它會讀取與 psql 相同的 PostgreSQL 連線環境變數；如有需要，請將 PGDATABASE 設為示範資料庫名稱。node-postgres 連線

將以下完整程式存為 upsert-articles.mjs。它假設設定資料表沒有會改變比對或更新行為的其他觸發程序或規則。值會以位置參數傳入，並與 SQL 文字分開。node-postgres 查詢

import pg from 'pg';
const { Pool } = pg;
const pool = new Pool();

async function upsertArticle(input) {
  const client = await pool.connect();
  let discardClient = false;

  try {
    await client.query('BEGIN ISOLATION LEVEL READ COMMITTED');
    await client.query(
      'LOCK TABLE articles IN SHARE ROW EXCLUSIVE MODE'
    );

    const matches = await client.query(
      `SELECT id FROM articles
       WHERE external_key = $1 OR slug = $2
       ORDER BY id`,
      [input.externalKey, input.slug]
    );

    if (matches.rows.length > 1) {
      const error = new Error(
        'Incoming keys identify two different articles'
      );
      error.code = 'ARTICLE_KEY_CONFLICT';
      throw error;
    }

    const values = [input.externalKey, input.slug, input.title];
    const result = matches.rows.length === 0
      ? await client.query(
          `INSERT INTO articles (external_key, slug, title)
           VALUES ($1, $2, $3)
           RETURNING id, external_key, slug, title`,
          values
        )
      : await client.query(
          `UPDATE articles
           SET external_key = $1, slug = $2, title = $3
           WHERE id = $4
           RETURNING id, external_key, slug, title`,
          [...values, matches.rows[0].id]
        );

    if (result.rows.length !== 1) {
      throw new Error('Expected exactly one article');
    }

    await client.query('COMMIT');
    return result.rows[0];
  } catch (error) {
    try {
      await client.query('ROLLBACK');
    } catch {
      discardClient = true;
    }
    throw error;
  } finally {
    client.release(discardClient);
  }
}

try {
  console.log(await upsertArticle({
    externalKey: 'feed-101',
    slug: 'blue-notes',
    title: 'Revised blue notes'
  }));
} finally {
  await pool.end();
}

依照 node-postgres 的要求，每個交易敘述式都使用同一個借出的用戶端。若透過個別的 pool.query() 呼叫送出這些敘述式，就無法維持連線一致性。node-postgres 交易

finally 區塊會一律釋放已取得的用戶端。若回復交易本身失敗，release(true) 會捨棄該連線。桌面指令碼結束時，外層的 pool.end() 會關閉連線集區。node-postgres 連線集區 API

使用 node upsert-articles.mjs 執行程式。預期結果是藍色文章的標題更新，而鍵值維持不變。JavaScript 語法已用 Node.js 檢查；資料庫範例已對照文件檢視，但此處未在 PostgreSQL 伺服器上執行。

檢查各個分支並明確處理失敗

使用設定資料列，變更傳給 upsertArticle() 的物件來測試下列情況。每個情況都應以原始設定狀態執行，或將先前情況造成的變更納入考量。

externalKey slug 預期結果
feed-303 amber-notes 插入新文章
feed-101 blue-revised 更新藍色文章並取代其 slug
feed-303 blue-notes 更新藍色文章並取代其外部鍵
feed-101 blue-notes 更新藍色文章的標題
feed-101 green-notes 擲出 ARTICLE_KEY_CONFLICT；兩篇文章都不變

查詢會回傳資料列，因此同時符合兩個條件的文章只會計算一次。在這兩個獨立且不可為空的唯一鍵下，最多只會有兩筆不同資料列符合。程式不會以 LIMIT 1 決定兩筆符合結果的處理方式、不會刪除文章，也不會悄悄略過輸入。

ARTICLE_KEY_CONFLICT 是應用程式自訂的代碼。PostgreSQL 的 23505 代表 unique_violation；兩者是不同情況。若調整範例後出現後者，請檢查遭違反的限制及新增的資料表行為，而不要把所有重複情況都視為相同結果。PostgreSQL 建議檢查錯誤代碼，而不是解析錯誤訊息文字。PostgreSQL 錯誤代碼

取得鎖定時可能需要等待。若希望此桌面工作立即失敗，請將鎖定敘述式替換為 LOCK TABLE articles IN SHARE ROW EXCLUSIVE MODE NOWAIT。若無法立即取得鎖定，PostgreSQL 會引發錯誤，而程式會執行回復路徑。PostgreSQL LOCK

DO NOTHING 或 MERGE 能否簡化這項工作？

不指定目標的 ON CONFLICT DO NOTHING 可以略過與任何可用唯一限制或索引衝突的插入。若略過是預期結果，就可選用此方式；它無法實作此處建立的更新策略。PostgreSQL INSERT 文件

MERGE 是在 PostgreSQL 15 中引入的。雖然它允許聯結條件涉及任一鍵，但若來源資料列符合兩篇不同的目標文章，仍需要一套處理策略。其動作仍受一般限制約束，而且其並行行為與指定目標 upsert 並不相同。PostgreSQL 15 版本說明、PostgreSQL MERGE

對於任一鍵皆可比對的需求，請明確依符合數量決策：零筆則插入，一筆則更新，兩筆則以清楚的衝突訊息停止。保留唯一限制，並選擇能讓這項判斷在寫入完成前保持有效的並行策略。若可接受序列化文章寫入，資料表鎖定實作可作為具體起點。

來源