在 PostgreSQL 中使用 EXCLUDE 值進行 Upsert(重複更新時插入、合併)
上次,我們讀到了如何在 PostgreSQL 中使用 UPSERT
。
在快速回顧中,UPSERT
是 INSERT ON DUPLICATE UPDATE
的縮寫,如果它們與以前的條目不匹配,則傾向於將 INSERT
值插入表中。如果有,它們會自動更新。
PostgreSQL 中的 EXCLUDED
是什麼
EXCLUDED
是 DBMS 給一個特殊表的名稱,在該表中我們有為 INSERTION
提議的所有行。一旦 INSERT
操作執行,這些行可能會插入到該表中。
這主要是在 ON CONFLICT DO UPDATE
子句之前,專門針對此表。此外,SET
和 WHERE
子句往往具有訪問此 EXCLUDED
表的許可權。
因此,下次你嘗試 INSERT
某些內容時,如果它似乎滿足你的需求,你可以使用 EXCLUDED
表。我們希望你清楚本文中使用的基本術語及其背後的機制。
讓我們開始學習一些方法,其中許多是簡短而有效的變通方法,以實現 EXCLUDED
表的使用。
PostgreSQL 中的基本 EXCLUDE
用法
可以實現第一個非常簡單的 EXCLUDE
用法。
-
讓我們為動物建立一個
TABLE
。create table animal( id int PRIMARY KEY, age int, type TEXT );
TYPE
這裡代表動物型別。可以是貓、狗、馬等。 -
讓我們插入一些值。
insert into animal values (1, 10, 'Dog'), (2, 12, 'Horse')
-
現在,讓我們繼續編寫
EXCLUDED
的查詢。insert into animal values (1, 3, 'Cat'), (3, 4, 'Kitten') on conflict (id) do update set id = excluded.id, age = excluded.age, type = excluded.type;
那麼這裡發生了什麼?首先,有一個副本。
集合 (1, 3, 'Cat')
違反了 PRIMARY KEY
的唯一約束,因為已經存在帶有鍵 1
的 (1, 10, 'Dog')
。
所以我們呼叫 ON CONFLICT DO UPDATE
,然後一旦它發現違規,我們將該行的鍵設定為正在插入的新資料集的鍵,從而覆蓋先前的條目。
輸出將是這樣的。
輸出:
但是,這取決於你是否要執行此操作。如果在插入時,你可能不想覆蓋,而是保持前一個條目完整,那麼在這種情況下,你根本不應該使用此查詢。
PostgreSQL 中基本 EXCLUDED
用法的結構差異
如果使用者可能試圖保持語句緊湊和可讀,他們可能會嘗試避免使用上述查詢,其中:
set id = excluded.id, age = excluded.age, type = excluded.type;
如果大量資料是 INSERTED
或 UPDATED
,這可能會變得混亂並隨後導致問題。
一個更簡單的方法是改用它。
set (id, age, type) = (EXCLUDED.id, EXCLUDED.age, EXCLUDED.type)
這傾向於對元素進行分組,並且與上面描述的幾乎相同,但更加有序和可讀。
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