在 PostgreSQL 中的 INSERT ON DUPLICATE UPDATE

1. PostgreSQL 中的 ON CONFLICT 子句
2. 在 PostgreSQL 中使用定製的觸發器或函式實現 Upsert
3. 在 PostgreSQL 中使用順序語句（不太推薦）實現 Upsert
4. 在 PostgreSQL 中 COMMON TABLE EXPRESSIONS (CTE) 實現 UPSERT

本文將介紹 PostgreSQL 中的 INSERT ON DUPLICATE UPDATE。插入重複更新 是什麼意思？為什麼要使用它？

每當你將插入記錄到表中時，都會新增具有唯一主鍵的資料集。它可以是每個資料集不同的 ID 或自動生成的數字。

假設你插入一條帶有主鍵的記錄，該記錄與已經存在的具有該主鍵的資料集衝突。

在這種情況下，你有兩個選擇。使用你的新資料集更新該行或保留更新並保留原始資料。

你傾向於選擇什麼取決於你希望如何對資料進行排序並將其儲存在資料庫中。

PostgreSQL 中的 ON CONFLICT 子句

Upsert 是 UPDATE ON INSERT VIOLATION 的縮寫，可以在 PostgreSQL 中的以下查詢中實現。首先，假設我們為 CAR 建立一個表並插入一行。

create table car(
	id int PRIMARY KEY,
	owner TEXT
);

insert into car values (1, 'John');

現在讓我們繼續嘗試在該表中插入一個值 (1, 'Mark')。你認為會發生什麼？

將發生的第一件事是會出現如下所示的 VIOLATION 錯誤。

輸出：

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

現在，你明白為什麼會發生主鍵違規了嗎？因為 Mark 也有已經存在於 John 的鍵 1。

為避免這種情況，我們將使用 ON CONFLICT 子句。

insert into car values (1, 'Mark')
on conflict (id) do update
set id = excluded.id,
	owner = excluded.owner;

現在，每當我們的查詢發現衝突時，它都會更新表中存在的該衝突主鍵的行，並將預先存在的資料的 ID 和所有者設定為這些新鍵。

但是，那個 EXCLUDED 表到底是什麼？EXCLUDED 表表示建議插入的行，如 PostgreSQL 文件所指定。

因此，我們使用 EXCLUDED 表來獲取從 INSERT 查詢推送的行，然後使用它們。

假設你不想對違規做任何事情並繼續保留原件。你最好使用這種語法。

insert into car values (1, 'Mark')
on conflict (id) do nothing;

DO NOTHING 忽略任何可能的更改。

要在 psql 中執行這些相同的查詢，請使用上述語句，但要大寫 KEYWORDS。永遠不要把 INSERT 寫成 insert，否則你會遇到錯誤。

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

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

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

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

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

select * from upsert_imp(1, 'Mark');

在進行 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 中 COMMON TABLE EXPRESSIONS (CTE) 實現 UPSERT

首先，什麼是 COMMON TABLE EXPRESSIONS？

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

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

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

WITH new_values (id, owner) as (
  values
     (2, 'Jackson'),
	 (5, 'Maria')

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

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

呼叫上述內容：

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

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

關於 PostgreSQL 中 ON CONFLICT DO UPDATE 的註釋

與每種解決方案一樣，一些問題通常會干擾效能或空間或編碼效率。

在我們的查詢中，每當我們呼叫 ON CONFLICT DO UPDATE 時，我們可以假設在此之後，在我們的系統中，可以有多個使用者同時訪問資料庫併發出命令。

因此，如果 USER 1 以 USER 2 的身份同時發出 INSERT DO UPDATE，則會再次出現 VIOLATION 錯誤。為什麼？

因為當 USER 2 呼叫 INSERT 時，它並不知道當時該行是否存在；因此它發生了衝突。兩個使用者同時發出查詢，資料庫同時執行它們。

這類問題被稱為 RACE CONDITION，如果你願意，你可以稍後閱讀它們。

隨著 RACE CONDITION 的出現，解決這個問題的方法也不同。人們爭辯說交易可能是一個可能的答案。

但是即使 TRANSACTION 保證將查詢分開，它也不能確保查詢與 VIOLATION 是安全的。為什麼？

如果 USER 1``TRANSACTS 是它的查詢，你不能保證一旦 TRANSACTION 執行，它會鎖定查詢並且不會讓其他查詢繼續進行。因為 TRANSACTIONS 只會阻止更改顯示給其他使用者，並且只能選擇放棄並回滾。

即使你使用聲稱按順序執行 QUERIES 的 SERIAL TRANSACTIONS，也可能出現如下錯誤。

commit failed: ERROR:  could not serialize access due to read/write dependencies among transactions

因此，你最好使用 ADVISORY LOCKS 和 SAVE POINTS。ADVISORY LOCKS 傾向於鎖定，防止你的查詢干擾和有效工作。

即使他們可能會重現錯誤，但這樣做的傾向仍然很低。

另一方面，SAVE POINTS 包含在 TRANSACTIONS 中。每當你想在 TRANSACTION 和回滾期間放棄更改時，你可以使用這些 SAVEPOINTS 恢復你的更改。

因此，如果 INSERT 失敗，例如，你只是沒有跳過錯誤，你現在可以回滾到 SAVE POINT，執行 UPDATE，然後照常工作。

我們希望你現在已經清楚 UPSERT 的基礎知識，並深入瞭解它的工作原理和可能的 CONSTRAINTS。

我們的團隊有效地工作，在知識和高效的編碼演算法方面為你提供兩全其美的體驗。我們相信，你越瞭解事物的角落和縫隙，你在提供解決方案和解決這些問題時就越有效。