PostgreSQL / 插入和修改資料

如何使用 `INSERT ON CONFLICT` 在 PostgreSQL 中執行 upsert 資料

內容

簡介
如何使用 INSERT...ON CONFLICT 建構
使用 DO NOTHING 動作
使用 DO UPDATE 動作
結論

分享到

簡介

PostgreSQL 讓您可以根據記錄是否已存在於表格中，來新增或修改表格中的記錄。這通常稱為 "upsert" 操作（"insert" 和 "update" 的混成詞）。

PostgreSQL 內的實際實作使用 INSERT 命令和特殊的 ON CONFLICT 子句來指定如果記錄已存在於表格中時該怎麼做。您可以指定如果記錄已存在於表格中，您想要更新記錄還是靜默跳過。

如何使用 `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 選項讓您可以在發生衝突時有條件地變更現有記錄，並且可以選擇性地使用原始建議列中的值。了解每個選項可能有用的情境，並學習如何使用這種通用格式，可以幫助簡化您在將新資料新增到現有資料集時的查詢。

關於作者