PostgreSQL 中的 Upsert（合併，重複更新時插入）

Upsert 指的是一種資料庫操作：鍵值是新的時插入資料列，鍵值已存在時則處理該資料列。PostgreSQL 透過 INSERT ... ON CONFLICT 提供這種行為。下方範例僅展示 SQL 語法；本文沒有提供 PostgreSQL 伺服器或執行證據。

PostgreSQL 中的 UPSERT 是什麼

UPSERT 結合了「更新」與「插入」的概念，但它不是 PostgreSQL 的 SQL 關鍵字。PostgreSQL 使用 INSERT ... ON CONFLICT 陳述式，在插入時處理衝突。DO UPDATE 動作必須使用可用的唯一性衝突目標；排除限制不能作為 DO UPDATE 的仲裁依據。DO NOTHING 動作則可以省略衝突目標，以忽略受可用唯一性限制或排除限制涵蓋的衝突。

假設 car 有一個整數 id 主鍵和一個 name 欄位，目前包含 (1, 'Nissan GTR')、(2, 'Toyota Corolla') 和 (3, 'Mercedes Benz')。如果直接 INSERT (2, 'Toyota Vigo')，會因為 id 2 已存在而違反主鍵限制。你可以依選擇的衝突動作，透過 upsert 改寫 id 2 的名稱，或讓該資料列維持原樣。

使用 INSERT ... ON CONFLICT 在 PostgreSQL 中執行 Upsert

在插入資料的同一個陳述式中使用 ON CONFLICT。例如，插入 (2, 'Toyota Vigo') 時加上 ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name，就會更新現有的 id 2 資料列。結果資料列會是 (2, 'Toyota Vigo')，其他資料列則不變。

衝突目標 (id) 指定哪個唯一鍵的衝突會觸發動作。它可以指定由唯一索引或限制所支援的欄位。如果指定限制名稱更清楚，PostgreSQL 也支援 ON CONSTRAINT constraint_name 形式。請選擇符合資料中重複判定規則的目標。若 DO NOTHING 陳述式省略目標，它可以忽略任何由可用唯一性限制或排除限制涵蓋的衝突；DO UPDATE 則必須指定衝突目標。

選擇 DO UPDATE 或 DO NOTHING

當衝突資料列應該更新或合併時，選擇 DO UPDATE。它的 SET 子句只列出要變更的欄位，因此可以保留主鍵不變。當應以現有資料列為準，並捨棄這次嘗試插入的資料時，選擇 DO NOTHING。例如，INSERT INTO car (id, name) VALUES (2, 'Toyota Vigo') ON CONFLICT (id) DO NOTHING 會讓 (2, 'Toyota Corolla') 維持不變。

這兩種動作代表不同的資料政策：更新可能覆蓋較新的值或使用者手動編輯的值；什麼都不做則可能留下過時的值。請依應用程式的需求決定哪種行為才正確，不要把任一動作視為在所有情況下都安全。單一 ON CONFLICT DO UPDATE 陳述式也不能在同一個命令中重複更新同一筆現有資料列。如果多資料列插入包含多個相同的衝突鍵值，請先將輸入資料去重，再送出。

使用 EXCLUDED 並選擇衝突目標

在 DO UPDATE 中，EXCLUDED 指的是提議插入但遇到衝突的資料列。它不是用來存放待處理資料列的資料表。在 SET name = EXCLUDED.name 中，PostgreSQL 會採用傳入的名稱；像 car.name 這樣的參照則指向衝突資料列目前儲存的值。你也可以合併這兩種值，例如以資料列目前的計數器值加上傳入的增量來更新計數器。

DO UPDATE 動作可以包含 WHERE 條件，只有符合應用程式規則時才變更資料列。即使條件阻止更新，PostgreSQL 仍會鎖定發生衝突的資料列。例如，版本檢查可以只在傳入版本較新時更新；但呼叫端仍應考量可能沒有任何資料列被更新。請一併選擇唯一衝突目標與更新條件：目標決定哪些資料列算是重複，條件則決定重複資料列何時變更。

為什麼自訂函式、觸發器、循序陳述式與 CTE 通常不是預設做法

自訂函式或觸發器可以集中處理特殊規則，但會增加呼叫端必須理解與維護的程式碼路徑。觸發器也會在插入或更新時作為副作用執行，可能讓行為較不容易察覺。一般的插入或更新行為，優先使用內建的衝突子句；只有在自訂邏輯能清楚表達陳述式無法表達的特定規則時，才採用自訂邏輯。

先檢查再插入的流程，例如先查詢 id 2 是否存在，不存在時才插入，會有競爭條件：另一個交易可能在檢查之後插入該 id。只用交易包住這兩個陳述式，並不會自動讓檢查與插入對並行工作階段成為原子操作。唯一性限制可以保護資料，而 INSERT ... ON CONFLICT 可讓 PostgreSQL 在限制處理時解決競爭插入。如果多步驟工作流程需要更強的保證，請選擇合適的鎖定或隔離策略，並在設計中處理序列化或唯一性錯誤。

通用資料表運算式（CTE）的作用範圍僅限於以 WITH 開始的單一 SQL 陳述式。CTE 可以整理一個陳述式，但 CTE 名稱無法供另一個獨立執行的 INSERT 或後續陳述式使用。因此，不能把含有更新 CTE 的 WITH 子句與後續獨立插入拆成不同呼叫，並把 CTE 當成暫存資料表。一般的插入或更新工作，應在同一個 INSERT ... ON CONFLICT 陳述式中處理衝突。只有在 CTE 能讓真正由多個部分組成的單一陳述式更易讀時，才使用 CTE。