PostgreSQL 中的 Upsert(合併,重複更新時插入)

Bilal Shahid 2023年1月30日 2022年5月14日
  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 操作。

什麼是 PostgreSQL 中的 UPSERT

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

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

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

假設我們有以下資料。

樣本資料

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

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

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

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

使用 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 KEYDO UPDATE 操作?

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

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

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

我們將解釋如何製作一個 FUNCTION,它會在呼叫 INSERTUPDATE 以實現 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');

現在將返回如下輸出。

使用函式實現 upsert

在進行 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

BEGINCOMMITINSERT 語句括起來,以確保它現在是 TRANSACTION

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

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

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

首先,什麼是公用表表示式(CTE)?

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

查詢可以寫成如下。我們首先定義 WITH,它建立一個臨時表,其中包含值 2, Toyota Supra5, 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 實現的可能方法。我們希望這對你有所幫助!

Author: Bilal Shahid
Bilal Shahid avatar Bilal Shahid avatar

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

相關文章 - PostgreSQL Upsert