如何在 PostgreSQL 中使用 `INSERT ON CONFLICT` 進行資料 Upsert

簡介

PostgreSQL 允許您根據記錄是否已存在來在表中新增或修改記錄。這通常被稱為“upsert”操作（“insert”和“update”的合成詞）。

PostgreSQL 中的實際實現使用帶有特殊 ON CONFLICT 子句的 INSERT 命令來指定如果記錄已存在於表中應執行的操作。您可以指定如果記錄已存在於表中時是更新該記錄還是靜默跳過。

如何使用 INSERT...ON CONFLICT 構造

插入或更新操作的基本語法如下：

INSERT INTO my_table (column1, column2)
VALUES
    (value1, value2),
    (value3, value4),
    (value5, value6),
    (value7, value8)
ON CONFLICT <target> <action>;

在此上下文中，<target> 指定了您要為其定義策略的衝突。它可以是以下任何一種：

• 特定列的名稱：(column1)
• 唯一約束的名稱：ON CONSTRAINT <constraint_name>

伴隨的 <action> 項將定義如果發生衝突，PostgreSQL 應執行的操作。指定的 <action> 可以是以下之一：

• DO NOTHING：告訴 PostgreSQL 保持衝突記錄不變。本質上，此操作不進行任何更改，但會抑制嘗試插入違反條件的行時通常會發生的錯誤。
• DO UPDATE：這告訴 PostgreSQL 您要更新表中已存在的行。更新的語法與正常的 UPDATE 命令的語法相同。

當指定 DO UPDATE 時，一個名為 EXCLUDED 的特殊虛擬表可用於 UPDATE 子句中。該表包含原始 INSERT 命令中建議的值（與現有表值衝突的值）。

注意：如果您使用 Prisma Client 連線到資料庫，則可以使用專用的 upsert 操作執行 upsert 操作。

使用 DO NOTHING 操作

對於我們的示例，假設我們有一個名為 director 的表。

CREATE TABLE director (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    latest_film TEXT
);

INSERT INTO director (name)
VALUES
    ('frank'),
    ('bob'),
    ('sue');

讓我們看看 PostgreSQL 通常如何處理擬議行與現有資料衝突的插入操作。假設已經有一個 id 為 3 的 director，PostgreSQL 將丟擲錯誤

INSERT INTO director (id, name)
VALUES
    (3, 'susan'),
    (4, 'delores');

ERROR:  duplicate key value violates unique constraint "director_pkey"
DETAIL:  Key (id)=(3) already exists.

在這種情況下，即使只有第一條記錄發生衝突，也沒有新增任何擬議記錄。如果我們想繼續新增任何沒有衝突的行，我們可以使用 ON CONFLICT DO NOTHING 子句。

在這裡，我們告訴 PostgreSQL 如果發生衝突則繼續，並繼續處理其他行

INSERT INTO director (id, name)
VALUES
    (3, 'susan'),
    (4, 'delores')
ON CONFLICT (id) DO NOTHING;

INSERT 0 1

如果您查詢該表，它將顯示即使第一條記錄與現有記錄之一發生衝突，第二條記錄仍被新增。

SELECT * FROM director;

 id |  name   | latest_film
----+---------+--------------
  3 | sue     |
  1 | frank   | second movie
  2 | bob     | fourth movie
  4 | delores |
(4 rows)

使用 DO UPDATE 操作

如果，我們想在行已存在於表中時更新它們，我們可以使用 ON CONFLICT DO UPDATE 子句。

這次，我們將執行與之前相同型別的查詢，但這次，如果發生衝突，我們將更新現有記錄

INSERT INTO director (id, name)
VALUES
    (2, 'robert'),
    (5, 'sheila'),
    (6, 'flora')
ON CONFLICT (id) DO UPDATE
SET name = EXCLUDED.name;

INSERT 0 3

這次，我們指定了如果與我們提議的插入之一衝突，則對現有行進行的修改。我們使用虛擬 EXCLUDED 表（其中包含我們打算插入的項）在發生衝突時將 name 列更新為新值。

您可以透過輸入以下內容來顯示所有記錄都已更新或新增

SELECT * FROM director;

 id |  name   | latest_film
----+---------+--------------
  3 | sue     |
  1 | frank   | second movie
  4 | delores |
  2 | robert  | fourth movie
  5 | sheila  |
  6 | flora   |
(6 rows)

結論

PostgreSQL 的 INSERT...ON CONFLICT 構造允許您在擬議記錄與現有記錄衝突時選擇兩種選項。 DO NOTHING 和 DO UPDATE 都各有其用途，具體取決於您要新增的資料與現有內容的關係。

DO NOTHING 選項允許您靜默跳過沖突行，從而可以新增任何不衝突的其他記錄。同時，DO UPDATE 選項允許您在發生衝突時有條件地修改現有記錄，並可選擇使用原始擬議行中的值。瞭解每種情況可能有用的場景並學習如何使用這種通用格式可以幫助您在向現有資料集新增新資料時簡化查詢。