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

1. 什麼是 PostgreSQL 中的 UPSERT
2. 在 PostgreSQL 中使用 ON CONFLICT (PK_Name) DO UPDATE 操作進行 Upsert
3. 在 PostgreSQL 中使用定製的觸發器或函式實現 Upsert
4. 在 PostgreSQL 中使用順序語句（不太推薦）實現 Upsert
5. 在 PostgreSQL 中使用公用表表示式 (CTE) 實現 UPSERT

本文描述瞭如何在 PostgreSQL 中執行 upsert 操作。

什麼是 PostgreSQL 中的 UPSERT

UPSERT 是在資料庫查詢中結合 UPDATE 和 INSERT 操作創造的一個術語。它不是我們最常討論的兩個 DBMS 中的關鍵字 MySQL 或 PostgreSQL。

因此，我們不能使用 UPSERT，無論是從句還是運算子。但是，它可能作為適當的子句存在於其他資料庫系統中。

UPSERT 很簡單；如果你想將資料插入表中，它將更新舊資料集，並且不知何故，該資料之前已經存在（重複）。如果之前不存在，它將插入資料集而不進行修改。

假設我們有以下資料。

現在我們要插入以下語句。

Insert into car values(2, 'Toyota Vigo')

INSERT 將返回重複錯誤，因為鍵已經存在。

但是，如果我們使用 UPSERT 方法執行此操作，資料將被更新，結果將是：

請記住，沒有特定的 UPSERT 方法，只有一個術語，用於表示能夠執行此類操作的一組操作。

在 MySQL 中，你可以使用以下語句實現 UPSERT。

INSERT INTO car values (2, 'Toyota Corolla') ON DUPLICATE KEY UPDATE name = 'Toyota Vigo';

這將檢測表中的 DUPLICATE KEY -> 2，然後呼叫 UPDATE 方法來更新該行。

現在讓我們繼續學習如何在 PostgreSQL 中實現它。

在 PostgreSQL 中使用 ON CONFLICT (PK_Name) DO UPDATE 操作進行 Upsert

讓我們使用我們上面製作的相同的 CAR 表。假設我們想在 PostgreSQL 的表中插入一些資料集。

insert into car values(2, 'Toyota Vigo')
ON CONFLICT (id) DO UPDATE
							SET id = excluded.id,
								name = excluded.name
;

你是否看到 ON CONFLICT 後跟 PRIMARY KEY 和 DO UPDATE 操作？

好吧，它檢查 PRIMARY KEY，在我們的例子中是 ID，如果它找到 CONFLICT，它將 UPDATE 而不是丟擲錯誤。你將在查詢中看到術語 EXCLUDED。

EXCLUDED 是一個表，其中包含建議在 PostgreSQL 中插入的行。因此，當你碰巧為新資料集呼叫 INSERT 時，你的資料最終會附加到 EXCLUDED 表中。

在 PostgreSQL 中使用定製的觸發器或函式實現 Upsert

我們將解釋如何製作一個 FUNCTION，它會在呼叫 INSERT 或 UPDATE 以實現 UPSERT 時觸發。同樣，你可以通過將返回型別更改為 TRIGGER 來建立 TRIGGER。

create or replace function upsert_imp (idt int, namet TEXT) returns void as
$$
	Begin
		loop
			update car set name = namet where id = idt;
		if found then
			return;
		end if;
		begin
			insert into car values(namet, idt);
			return;
		end;
		end loop;
	end;
$$ language plpgsql;

該函式非常簡單，執行一個迴圈來檢查每一行的 ID；如果它與給定的引數匹配，則返回 UPDATE 之後的表，否則返回 INSERTS。

你可以像下面這樣呼叫上面的。

select * from upsert_imp(2, 'Toyota Supra');

現在將返回如下輸出。

在進行 TRIGGER 時，請確保將 LOOP 替換為 FOR 或有效的 IF 檢查，以免它無限期地旋轉而違反條件。

在 PostgreSQL 中使用順序語句（不太推薦）實現 Upsert

你可以使用 UPDATE 呼叫，但將其與以下 INSERT 語句結合使用。

insert into car values(4, 'Toyota Supra') on conflict do nothing;

你甚至可以使用 NOT EXISTS 子句，但這個簡單的語句可以正常工作。如果沒有重複，它將插入該行或完全跳過。

insert into car select 2, 'Toyota Supra' where not exists (Select 1 from CAR where id=2);

但是，有一個條件。在許多系統中，可能存在 RACE 條件。

如果有人在你 INSERT 一行的同時 DELETES 一行，你的行將丟失。為此，你可以使用 TRANSACTION。

用 BEGIN 和 COMMIT 將 INSERT 語句括起來，以確保它現在是 TRANSACTION。

begin;
insert into car select 2, 'Toyota Supra' where not exists (Select 1 from CAR where id=2);
commit;

但是，PostgreSQL 已經在每個語句中新增了隱式的 BEGIN 和 COMMIT，因此不需要顯式宣告。

在 PostgreSQL 中使用公用表表示式 (CTE) 實現 UPSERT

首先，什麼是公用表表示式（CTE）？

CTE 用作查詢中的臨時表，以儲存稍後可能使用的值。它的行為類似於 TRIGGER 中使用的 NEW 表。

查詢可以寫成如下。我們首先定義 WITH，它建立一個臨時表，其中包含值 2, Toyota Supra 和 5, Honda City。

然後這些值被傳遞到 UPSERT 查詢中，它從 NEW_VALUES 表中建立 NV，如果它們已經存在於 CAR 表中，它會相應地更新值。

WITH new_values (id, name) as (
  values
     (2, 'Toyota Supra'),
	 (5, 'Honda City')

),
upsert as
(
    update car
        set id = nv.id,
            name = nv.name
    FROM new_values nv
    WHERE car.id = nv.id
    RETURNING car.*
)

這將返回一個 CAR.*，表示 CAR 表中的所有行。

呼叫上述內容：

INSERT INTO car (id, name)
SELECT id, name
FROM new_values
WHERE NOT EXISTS (SELECT 1
                  FROM upsert up
                  WHERE up.id = new_values.id);

在這裡，我們檢查在 UPSERT 中建立的表（當它返回 CAR.*時）是否已經包含該值；如果沒有，INSERT INTO 有效。但是，如果是這樣，UPSERT 已經在內部處理了修改。

這些是在 PostgreSQL 中建立 UPSERT 實現的可能方法。我們希望這對你有所幫助！