在 PostgreSQL 中的 INSERT ON DUPLICATE UPDATE
-
PostgreSQL 中的
ON CONFLICT
子句 -
在 PostgreSQL 中使用定製的
觸發器
或函式
實現 Upsert - 在 PostgreSQL 中使用順序語句(不太推薦)實現 Upsert
-
在 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
。
我們的團隊有效地工作,在知識和高效的編碼演算法方面為你提供兩全其美的體驗。我們相信,你越瞭解事物的角落和縫隙,你在提供解決方案和解決這些問題時就越有效。
Hello, I am Bilal, a research enthusiast who tends to break and make code from scratch. I dwell deep into the latest issues faced by the developer community and provide answers and different solutions. Apart from that, I am just another normal developer with a laptop, a mug of coffee, some biscuits and a thick spectacle!
GitHub