所謂的 Upsert 功能用來進行資料衝突判斷，決定資料要新增還是更新，簡單的避開上述困擾。在 Postgres 9.5 之後，引入了 Upsert 功能，INSERT . . . ON CONFLICT DO . . .，可以達成上述功能。PGSQL 的 Upsert 把資料先嘗試以 INSERT 塞入表格，發生衝突時才進行更新或略過資料。
而在 Oracle 資料庫的 Merge 功能，與 Upsert 有一點點小差別：MERGE INTO 是一次以多筆資料塞入表格。資料併入表格的方法以輸入或更新二選一的方式，而非先進行輸入失敗才更新
這裡紀錄 Oracle MERGE INTO 轉換成 PGSQL INSERT ON CONFLICT 的筆記。
根據 Oracle 手冊，提供的 Merge 語法可以達成以下功能
- 檢查 Unique Key Column，或是檢查 Primary Key Column(s) 是否與輸入資料有衝突，進行 Upsert 的功能
- 或是單純檢查給定條件，決定資料要被輸入還是更新。該狀況不在乎被檢查的欄位有沒有 Unique Key Constraint
- 針對 Insert 之前的來源資料作檢查，進一步再過濾輸入資料的條件之後才插入資料
- 針對 Update 之後的結果作檢查，進一步執行刪除
以下採用範例，參照這裡的 Oracle MERGE 範例，這例子完整的包含了 Merge 可以有的功能。
這指令的內容，主要在取出 employee 表格中 dept_no 為 20 的資料，拿來更新 bonuses 表格的內容，檢查條件為 employee_id 欄位的匹配。
employee_id 沒有發生衝突就插入資料（INSERT），且進一步限定 salary 欄位多於 40000 的不衝突資料才能插入（透過 WHERE 條件達成）；
遇到 employee_id 衝突的資料就使用更新（UPDATE），且進一步限定 salary 欄位不少於 40000 的資料被排除在外（透過 DELETE 達成）。
MERGE INTO bonuses b USING ( SELECT employee_id, salary, dept_no FROM employee WHERE dept_no =20) e ON (b.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET b.bonus = e.salary * 0.1 DELETE WHERE (e.salary < 40000) -- PGSQL 做不到這條 WHEN NOT MATCHED THEN INSERT (b.employee_id, b.bonus) VALUES (e.employee_id, e.salary * 0.05) WHERE (e.salary > 40000); COMMIT; ------------------------------------------------------------ SQL> -- Oracle 裡面，Merge 之前的資料 SQL> select * from bonuses; EMPLOYEE_ID BONUS ----------- ---------- 1 100 2 100 4 100 6 100 7 100 5 rows selected. ------------------------------------------------------------ SQL> -- Oracle 裡面，Merge 之後的結果 SQL> select * from bonuses; EMPLOYEE_ID BONUS ----------- ---------- 1 100 2 10000 4 4000 6 3000 7 9000 5 3500 3 2500 7 rows selected.
以下作一些 Merge 的調整以便和 PGSQL 作比較。
1) 由於 MERGE 的 DELETE 段落在 PGSQL 做不到，因此下面調整一下，作為比較參考
--不含 delete MERGE INTO bonuses b USING ( SELECT employee_id, salary, dept_no FROM employee WHERE dept_no =20) e ON (b.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET b.bonus = e.salary * 0.1 WHEN NOT MATCHED THEN INSERT (b.employee_id, b.bonus) VALUES (e.employee_id, e.salary * 0.05) WHERE (e.salary > 40000); COMMIT; ------------------------------------------------------------ SQL> -- Oracle 裡面的結果，供下面 PGSQL 作比較 SQL> select * from bonuses; EMPLOYEE_ID BONUS ----------- ---------- 1 100 2 10000 4 4000 6 3000 7 9000 5 3500 3 2500 7 rows selected.
2) 以下為最一般使用的 Merge 形式，單純的 Upsert 功能，不包含 INSERT 上的 WHERE 或是 UPDATE 上的 DELETE
--不含 delete MERGE INTO bonuses b USING ( SELECT employee_id, salary, dept_no FROM employee WHERE dept_no =20) e ON (b.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET b.bonus = e.salary * 0.1 WHEN NOT MATCHED THEN INSERT (b.employee_id, b.bonus) VALUES (e.employee_id, e.salary * 0.05) ; COMMIT; ------------------------------------------------------------ SQL> -- Oracle 裡面的結果，供下面 PGSQL 作比較 SQL> select * from bonuses; EMPLOYEE_ID BONUS ----------- ---------- 1 100 2 10000 4 4000 6 3000 7 9000 5 3500 3 2500 7 rows selected.
下面為 PGSQL 底下範例資料的準備
edb=# create schema merge_exsample;
CREATE SCHEMA
edb=# set search_path to merge_exsample;
SET
edb=# \e
CREATE TABLE employee (
employee_id NUMBER(5),
first_name VARCHAR2(20),
last_name VARCHAR2(20),
dept_no NUMBER(2),
salary NUMBER(10));
INSERT INTO employee VALUES (1, 'Dan', 'Morgan', 10, 100000),
(2, 'Helen', 'Lofstrom', 20, 100000), (3, 'Akiko', 'Toyota', 20, 50000), (4, 'Jackie', 'Stough', 20, 40000), (5, 'Richard', 'Foote', 20, 70000), (6, 'Joe', 'Johnson', 20, 30000), (7, 'Clark', 'Urling', 20, 90000);
CREATE TABLE bonuses (
employee_id NUMBER, bonus NUMBER DEFAULT 100);
INSERT INTO bonuses (employee_id) VALUES (1),(2),(4),(6),(7);
COMMIT;
CREATE TABLE
INSERT 0 7
CREATE TABLE
INSERT 0 5
COMMIT
edb=# SELECT * FROM bonuses;
employee_id | bonus
-------------+-------
1 | 100
2 | 100
4 | 100
6 | 100
7 | 100
(5 rows)
edb=#
以下紀錄兩種 PGSQL 的 Upsert 方法，與上面的 1) 與 2) 相互對照比較。
1) 在 PGSQL 要達成上面的 Oracle Merge 功能，可以透過 Writable Common-Table Expression 改寫；這是最一般流傳的手法
-- 用 NOT IN 作衝突判斷 WITH e AS( SELECT employee_id, salary, dept_no FROM employee WHERE dept_no =20), upsert AS( UPDATE bonuses b SET b.bonus = e.salary * 0.1 FROM e WHERE b.employee_id = e.employee_id RETURNING b.employee_id, e.salary ) INSERT INTO bonuses AS b (employee_id, bonus) SELECT e.employee_id, e.salary * 0.05 FROM e WHERE employee_id NOT IN (SELECT employee_id FROM upsert ) AND e.salary > 40000; -------------------------------------------------------------- edb=# --執行結果 edb=# SELECT * FROM bonuses; employee_id | bonus -------------+--------- 1 | 100 2 | 10000.0 4 | 4000.0 6 | 3000.0 7 | 9000.0 3 | 2500.00 5 | 3500.00 (7 rows) edb=# truncate bonuses; TRUNCATE TABLE edb=# INSERT INTO bonuses (employee_id) VALUES (1),(2),(4),(6),(7); INSERT 0 5
-- 用 NOT EXISTS 作衝突判斷 WITH e AS( SELECT employee_id, salary, dept_no FROM employee WHERE dept_no =20), upsert AS( UPDATE bonuses b SET b.bonus = e.salary * 0.1 FROM e WHERE b.employee_id = e.employee_id RETURNING b.employee_id, e.salary ) INSERT INTO bonuses AS b (employee_id, bonus) SELECT e.employee_id, e.salary * 0.05 FROM e WHERE NOT EXISTS (SELECT employee_id FROM upsert u WHERE u.employee_id =e.employee_id ) AND e.salary > 40000; ----------------------------------------------------------- edb=# --結果一樣 edb=# SELECT * FROM bonuses; employee_id | bonus -------------+--------- 1 | 100 2 | 10000.0 4 | 4000.0 6 | 3000.0 7 | 9000.0 3 | 2500.00 5 | 3500.00 (7 rows) edb=# truncate bonuses ; TRUNCATE TABLE edb=# INSERT INTO bonuses (employee_id) VALUES (1),(2),(4),(6),(7); INSERT 0 5
這裡除了實現典型的 Merge 用途，也把輸入資料篩選條件加了進去。和上面 Oracle Merge 範例 1) 一樣。
不過這裡沒辦法像 Oracle MERGE 一樣，在更新之時，還進一步檢查條件「再」刪掉被更新的資料。
2) 除了上述流傳許久的 Merge 設計，PGSQL 9.5 以上的版本引入的 Upsert 功能，主要針對 Unique Key 欄位作輸入資料衝突檢查。這個功能與 Oracle MERGE 比較起來：
- PGSQL INSERT ON CONFLICT 避開了 Race Condition 發生的可能，是近日最完整的 Upsert 實作
- PGSQL INSERT ON CONFLICT 在衝突檢查中看的是 Unique Key / Primary Key / Multi-Column Primary Key 欄位是否衝到
- 不能像 Oracle Merge 語法，使用等號條件式作衝突檢查，
- 只能放置要避免衝突的 Unique Key Constraint 欄位列表：資料庫會自動把待輸入的新資料和現存表格內的資料作比較
- 沒有 Insert 之前來源資料的 WHERE 條件檢查：
- 用 INSERT INTO SELECT 來處理，會針對「所有」準備匯入資料作過濾
- INSERT INTO SELECT . . . WHERE 會連帶影響到後面可以 Update 的資料；Oracle 中針對 Insert 的條件檢查與 Update 互相不影響
- 沒有 Update 之後被更新資料的 DELETE Check
開始前，要增加 Unique Key 到 bonuses 表格的 employee_id 上，因為 INSERT ON CONFLICT 要求要對 Unique Key 欄位作衝突檢查
-- 執行之前，先確保表格有 Unique Key alter table bonuses add unique(employee_id);
以下便是 ON CONFLICT . . . DO UPDATE 的改寫。這裡沒辦法在 Insert 上面用 WHERE 條件進行「再」過濾，也沒辦法在 Update 上面用 Delete 進行「再」過濾
WITH e AS ( SELECT employee_id, salary, dept_no FROM employee WHERE dept_no =20 ) INSERT into bonuses AS b (employee_id, bonus) SELECT e.employee_id, e.salary * 0.05 FROM e ON CONFLICT (employee_id) do update SET b.bonus = (SELECT e.salary * 0.1 FROM e WHERE b.employee_id = e.employee_id ) ; -------------------------------------------------------------- edb=# select * from bonuses ; employee_id | bonus -------------+--------- 1 | 100 2 | 10000.0 3 | 2500.00 4 | 4000.0 5 | 3500.00 6 | 3000.0 7 | 9000.0 (7 rows)
這裡和上面 Oracle Merge 範例 2) 一樣。一般來說，大家在 Oracle 上應該都是用到範例 2) 居多。
如前所述，在 INSERT INTO SELECt . . . ON CONFLICT 中使用 WHERE 條件，效應會影響到 Update，這行為跟 Oracle MERGE 在 INSERT 上指定 WHERE 條件的行為不一樣：
WITH e AS ( SELECT employee_id, salary, dept_no FROM employee WHERE dept_no =20 ) INSERT into bonuses AS b (employee_id, bonus) SELECT e.employee_id, e.salary * 0.05 FROM e WHERE e.salary > 40000 -- 會連帶影響後面作更新的資料 on conflict (employee_id) do update SET b.bonus = (SELECT e.salary * 0.1 FROM e WHERE b.employee_id = e.employee_id ) ; -------------------------------------------------------------- edb=# SELECT * FROM bonuses; employee_id | bonus -------------+--------- 1 | 100 4 | 100 6 | 100 2 | 10000.0 3 | 2500.00 5 | 3500.00 7 | 9000.0 (7 rows)
上面可以看到紅底部份沒有被更新。
到目前為止，ON CONFLICT 的功能正如其名，還只是資料衝突的解決（Upsert）：依據準備輸入的一批資料，並準備好當中有發生資料衝突時的應對方式（更新 / 忽略）。
這樣的 Upsert 沒辦法在輸入資料上再針對資料下檢查條件；也沒辦法在更新後的資料上，增加異動的剔除。
因此，INSERT ON CONFLICT 並不能完全替代 Oracle MERGE 的功能：Oracle Merge 的衝突檢查不限制在 Key Violation 上；而且看起來是完全以一組資料「以輸入或更新」二選一的方式併入指定表格內。
某些沒有將表格作 First Normal Form 的場合，無法用 PGSQL 的 Upsert。因此 PGSQL 目前局限在 Upsert 變得用途會比 Oracle MERGE 要少一些。不過應用上都還是取決於程式的規劃和設計，而不完全是工具本身的能力的問題，所謂山不轉路轉啊～～
不過 PGSQL 新引進的 Upsert 功能實作上，確保會避開可能的 Race Condition，是目前比他款關聯式資料庫先進的地方。
現在 PGSQL 9.6 Upsert 語法限制（應該到 PGSQL 10 也是一樣）
- 在 ON CONFLICT 內，不能用 table alias 放在 column name 之前
- 不能用 UPDATE table_A SET . . . FROM table_B WHERE . . . 這種 Join 的寫法
期待後續 PGSQL 或是企業版能擴充語法，變成一個完整的 Merge 功能～
參考資料
Upsert 功能的資料
通用 Merge 替代方式的資料
對照
沒有留言:
張貼留言