PostgreSQL 中的 Upsert(合併,重複更新時插入)
-
什麼是 PostgreSQL 中的
UPSERT
-
在 PostgreSQL 中使用
ON CONFLICT (PK_Name) DO UPDATE
操作進行 Upsert -
在 PostgreSQL 中使用定製的
觸發器
或函式
實現 Upsert - 在 PostgreSQL 中使用順序語句(不太推薦)實現 Upsert
-
在 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
實現的可能方法。我們希望這對你有所幫助!
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