标簽
PostgreSQL , MERGE , CTE , trigger , rule
https://github.com/digoal/blog/blob/master/201804/20180407_01.md#%E8%83%8C%E6%99%AF 背景
PostgreSQL 11 支援了merge 文法，相容SQL 2016标準。并且支援用于CTE文法中。
merge 文法常用于合并資料（将 某個源表、values表達式、QUERY、臨時表等 合并到某個目标表中）。
例如，将源表的變更日志，合并到物化視圖中。
PostgreSQL 除了使用insert into on conflict，如今又多了merge文法的支援（可以更好的支援OLAP的資料批量合并場景）。
https://github.com/digoal/blog/blob/master/201804/20180407_01.md#%E8%AF%AD%E6%B3%95 文法
https://www.postgresql.org/docs/devel/static/sql-merge.html[ WITH with_query [, ...] ]
MERGE INTO target_table_name [ [ AS ] target_alias ]
USING data_source
ON join_condition
when_clause [...]
where data_source is
{ source_table_name |
( source_query )
}
[ [ AS ] source_alias ]
and when_clause is
{ WHEN MATCHED [ AND condition ] THEN { merge_update | merge_delete } |
WHEN NOT MATCHED [ AND condition ] THEN { merge_insert | DO NOTHING }
}
and merge_insert is
INSERT [( column_name [, ...] )]
[ OVERRIDING { SYSTEM | USER } VALUE ]
{ VALUES ( { expression | DEFAULT } [, ...] ) | DEFAULT VALUES }
and merge_update is
UPDATE SET { column_name = { expression | DEFAULT } |
( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] )
} [, ...]
and merge_delete is
DELETE
https://github.com/digoal/blog/blob/master/201804/20180407_01.md#%E8%BF%94%E5%9B%9E 傳回
MERGE total-count
https://github.com/digoal/blog/blob/master/201804/20180407_01.md#%E7%9B%AE%E6%A0%87%E8%A1%A8%E8%A7%A6%E5%8F%91%E5%99%A8%E5%8F%AF%E8%83%BD%E8%A2%AB%E8%A7%A6%E5%8F%91 目标表觸發器可能被觸發
觸發規則詳見
https://github.com/digoal/blog/blob/master/201804/20180407_01.md#%E7%9B%AE%E6%A0%87%E8%A1%A8%E8%A7%84%E5%88%99%E4%B8%8D%E4%BC%9A%E8%A2%AB%E8%A7%A6%E5%8F%91 目标表規則不會被觸發
如果JOIN過程中，比對到多行，根據SQL标準定義來處理，目前是這樣的，如果是更新的話，多行比對（目标表的某單行，被源表或源資料的多行比對到）會導緻報錯。
https://github.com/digoal/blog/blob/master/201804/20180407_01.md#%E4%B8%80%E4%BA%9B%E4%BE%8B%E5%AD%90 一些例子
1、Perform maintenance on CustomerAccounts based upon new Transactions.
MERGE INTO CustomerAccount CA -- 目标表
USING RecentTransactions T -- 源可以是表、query、(values (),(),....) as alias表達式 等
ON T.CustomerId = CA.CustomerId -- JOIN 條件
WHEN MATCHED THEN -- 記錄比對，可以引用使用源于目标中的字段
UPDATE SET Balance = Balance + TransactionValue
WHEN NOT MATCHED THEN -- 記錄未比對，隻能引用源字段
INSERT (CustomerId, Balance)
VALUES (T.CustomerId, T.TransactionValue);
2、notice that this would be exactly equivalent to the following statement because the MATCHED result does not change during execution
MERGE INTO CustomerAccount CA
USING (Select CustomerId, TransactionValue From RecentTransactions) AS T
ON CA.CustomerId = T.CustomerId
WHEN NOT MATCHED THEN
INSERT (CustomerId, Balance)
VALUES (T.CustomerId, T.TransactionValue)
WHEN MATCHED THEN
UPDATE SET Balance = Balance + TransactionValue;
3、Attempt to insert a new stock item along with the quantity of stock. If the item already exists, instead update the stock count of the existing item. Don't allow entries that have zero stock.
MERGE INTO wines w
USING wine_stock_changes s -- 源可以是臨時表
ON s.winename = w.winename
WHEN NOT MATCHED AND s.stock_delta > 0 THEN
INSERT VALUES(s.winename, s.stock_delta)
WHEN MATCHED AND w.stock + s.stock_delta > 0 THEN
UPDATE SET stock = w.stock + s.stock_delta;
WHEN MATCHED THEN
DELETE;
The wine_stock_changes table might be, for example, a temporary table recently loaded into the database.
4、回歸測試中的例子
+-- WITH referenced by MERGE statement
+CREATE TABLE m AS SELECT i AS k, (i || ' v')::text v FROM generate_series(1, 16, 3) i;
+ALTER TABLE m ADD UNIQUE (k);
+WITH RECURSIVE cte_basic AS (SELECT 1 a, 'cte_basic val' b)
+MERGE INTO m USING (select 0 k, 'merge source SubPlan' v) o ON m.k=o.k
+WHEN MATCHED THEN UPDATE SET v = (SELECT b || ' merge update' FROM cte_basic WHERE cte_basic.a = m.k LIMIT 1)
+WHEN NOT MATCHED THEN INSERT VALUES(o.k, o.v);
+ERROR: WITH RECURSIVE is not supported for MERGE statement
+-- Basic:
+WITH cte_basic AS (SELECT 1 a, 'cte_basic val' b)
+MERGE INTO m USING (select 0 k, 'merge source SubPlan' v) o ON m.k=o.k
+WHEN MATCHED THEN UPDATE SET v = (SELECT b || ' merge update' FROM cte_basic WHERE cte_basic.a = m.k LIMIT 1)
+WHEN NOT MATCHED THEN INSERT VALUES(o.k, o.v);
+-- Examine
+SELECT * FROM m where k = 0;
+ k | v
+---+----------------------
+ 0 | merge source SubPlan
+(1 row)
+
+-- See EXPLAIN output for same query:
+EXPLAIN (VERBOSE, COSTS OFF)
+WITH cte_basic AS (SELECT 1 a, 'cte_basic val' b)
+MERGE INTO m USING (select 0 k, 'merge source SubPlan' v) o ON m.k=o.k
+WHEN MATCHED THEN UPDATE SET v = (SELECT b || ' merge update' FROM cte_basic WHERE cte_basic.a = m.k LIMIT 1)
+WHEN NOT MATCHED THEN INSERT VALUES(o.k, o.v);
+ QUERY PLAN
+-------------------------------------------------------------------
+ Merge on public.m
+ CTE cte_basic
+ -> Result
+ Output: 1, 'cte_basic val'::text
+ -> Hash Right Join
+ Output: o.k, o.v, o.*, m_1.ctid
+ Hash Cond: (m_1.k = o.k)
+ -> Seq Scan on public.m m_1
+ Output: m_1.ctid, m_1.k
+ -> Hash
+ Output: o.k, o.v, o.*
+ -> Subquery Scan on o
+ Output: o.k, o.v, o.*
+ -> Result
+ Output: 0, 'merge source SubPlan'::text
+ SubPlan 2
+ -> Limit
+ Output: ((cte_basic.b || ' merge update'::text))
+ -> CTE Scan on cte_basic
+ Output: (cte_basic.b || ' merge update'::text)
+ Filter: (cte_basic.a = m.k)
+(21 rows)
+
+-- InitPlan
+WITH cte_init AS (SELECT 1 a, 'cte_init val' b)
+MERGE INTO m USING (select 1 k, 'merge source InitPlan' v) o ON m.k=o.k
+WHEN MATCHED THEN UPDATE SET v = (SELECT b || ' merge update' FROM cte_init WHERE a = 1 LIMIT 1)
+WHEN NOT MATCHED THEN INSERT VALUES(o.k, o.v);
+-- Examine
+SELECT * FROM m where k = 1;
+ k | v
+---+---------------------------
+ 1 | cte_init val merge update
+(1 row)
+
+-- See EXPLAIN output for same query:
+EXPLAIN (VERBOSE, COSTS OFF)
+WITH cte_init AS (SELECT 1 a, 'cte_init val' b)
+MERGE INTO m USING (select 1 k, 'merge source InitPlan' v) o ON m.k=o.k
+WHEN MATCHED THEN UPDATE SET v = (SELECT b || ' merge update' FROM cte_init WHERE a = 1 LIMIT 1)
+WHEN NOT MATCHED THEN INSERT VALUES(o.k, o.v);
+ QUERY PLAN
+--------------------------------------------------------------------
+ Merge on public.m
+ CTE cte_init
+ -> Result
+ Output: 1, 'cte_init val'::text
+ InitPlan 2 (returns $1)
+ -> Limit
+ Output: ((cte_init.b || ' merge update'::text))
+ -> CTE Scan on cte_init
+ Output: (cte_init.b || ' merge update'::text)
+ Filter: (cte_init.a = 1)
+ -> Hash Right Join
+ Output: o.k, o.v, o.*, m_1.ctid
+ Hash Cond: (m_1.k = o.k)
+ -> Seq Scan on public.m m_1
+ Output: m_1.ctid, m_1.k
+ -> Hash
+ Output: o.k, o.v, o.*
+ -> Subquery Scan on o
+ Output: o.k, o.v, o.*
+ -> Result
+ Output: 1, 'merge source InitPlan'::text
+(21 rows)
+
+-- MERGE source comes from CTE:
+WITH merge_source_cte AS (SELECT 15 a, 'merge_source_cte val' b)
+MERGE INTO m USING (select * from merge_source_cte) o ON m.k=o.a
+WHEN MATCHED THEN UPDATE SET v = (SELECT b || merge_source_cte.*::text || ' merge update' FROM merge_source_cte WHERE a = 15)
+WHEN NOT MATCHED THEN INSERT VALUES(o.a, o.b || (SELECT merge_source_cte.*::text || ' merge insert' FROM merge_source_cte));
+-- Examine
+SELECT * FROM m where k = 15;
+ k | v
+----+--------------------------------------------------------------
+ 15 | merge_source_cte val(15,"merge_source_cte val") merge insert
+(1 row)
+
+-- See EXPLAIN output for same query:
+EXPLAIN (VERBOSE, COSTS OFF)
+WITH merge_source_cte AS (SELECT 15 a, 'merge_source_cte val' b)
+MERGE INTO m USING (select * from merge_source_cte) o ON m.k=o.a
+WHEN MATCHED THEN UPDATE SET v = (SELECT b || merge_source_cte.*::text || ' merge update' FROM merge_source_cte WHERE a = 15)
+WHEN NOT MATCHED THEN INSERT VALUES(o.a, o.b || (SELECT merge_source_cte.*::text || ' merge insert' FROM merge_source_cte));
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------
+ Merge on public.m
+ CTE merge_source_cte
+ -> Result
+ Output: 15, 'merge_source_cte val'::text
+ InitPlan 2 (returns $1)
+ -> CTE Scan on merge_source_cte merge_source_cte_1
+ Output: ((merge_source_cte_1.b || (merge_source_cte_1.*)::text) || ' merge update'::text)
+ Filter: (merge_source_cte_1.a = 15)
+ InitPlan 3 (returns $2)
+ -> CTE Scan on merge_source_cte merge_source_cte_2
+ Output: ((merge_source_cte_2.*)::text || ' merge insert'::text)
+ -> Hash Right Join
+ Output: merge_source_cte.a, merge_source_cte.b, ROW(merge_source_cte.a, merge_source_cte.b), m_1.ctid
+ Hash Cond: (m_1.k = merge_source_cte.a)
+ -> Seq Scan on public.m m_1
+ Output: m_1.ctid, m_1.k
+ -> Hash
+ Output: merge_source_cte.a, merge_source_cte.b
+ -> CTE Scan on merge_source_cte
+ Output: merge_source_cte.a, merge_source_cte.b
+(20 rows)
+
+DROP TABLE m;
5、個人測試例子
目标表觸發器
create or replace function tg() returns trigger as $$
declare
begin
raise notice '%', new;
return null;
end;
$$ language plpgsql strict;
目标表
create table t1(id int primary key, info text, crt_time timestamp);
源表
create table t2(id int primary key, info text, crt_time timestamp);
寫入100條測試資料到源表
postgres=# insert into t2 select generate_series(1,100),random()*100, now();
INSERT 0 100
建立目标表觸發器
postgres=# create trigger tg after insert on t1 for each row execute procedure tg();
CREATE TRIGGER
使用merge into，将源表資料合并到目标表，并觸發了INSERT觸發器
postgres=#
merge into t1 using t2
on t1.id=t2.id
when not matched then insert (id,info,crt_time) values (t2.id,t2.info,t2.crt_time)
when matched and t1.info<>t2.info or t1.crt_time<>t2.crt_time then update set info=t2.info , crt_time=t2.crt_time;
NOTICE: (1,29.9749359954149,"2018-04-07 22:03:31.531152")
NOTICE: (2,93.1852474343032,"2018-04-07 22:03:31.531152")
NOTICE: (3,25.758066913113,"2018-04-07 22:03:31.531152")
NOTICE: (4,69.128438225016,"2018-04-07 22:03:31.531152")
NOTICE: (5,34.7714505158365,"2018-04-07 22:03:31.531152")
NOTICE: (6,47.4942798726261,"2018-04-07 22:03:31.531152")
NOTICE: (7,61.8766254279763,"2018-04-07 22:03:31.531152")
NOTICE: (8,97.1885625738651,"2018-04-07 22:03:31.531152")
NOTICE: (9,7.41707538254559,"2018-04-07 22:03:31.531152")
NOTICE: (10,70.8636813331395,"2018-04-07 22:03:31.531152")
NOTICE: (11,33.8312264997512,"2018-04-07 22:03:31.531152")
NOTICE: (12,77.4763741064817,"2018-04-07 22:03:31.531152")
NOTICE: (13,5.14419632963836,"2018-04-07 22:03:31.531152")
NOTICE: (14,86.3092178478837,"2018-04-07 22:03:31.531152")
NOTICE: (15,41.1678662057966,"2018-04-07 22:03:31.531152")
NOTICE: (16,27.8295359108597,"2018-04-07 22:03:31.531152")
NOTICE: (17,77.8380565810949,"2018-04-07 22:03:31.531152")
NOTICE: (18,13.997572241351,"2018-04-07 22:03:31.531152")
NOTICE: (19,34.8465123679489,"2018-04-07 22:03:31.531152")
NOTICE: (20,26.4941859059036,"2018-04-07 22:03:31.531152")
NOTICE: (21,88.9189361128956,"2018-04-07 22:03:31.531152")
NOTICE: (22,21.6747588012367,"2018-04-07 22:03:31.531152")
NOTICE: (23,41.413659369573,"2018-04-07 22:03:31.531152")
NOTICE: (24,19.375761738047,"2018-04-07 22:03:31.531152")
NOTICE: (25,53.5534802824259,"2018-04-07 22:03:31.531152")
NOTICE: (26,82.7209649607539,"2018-04-07 22:03:31.531152")
NOTICE: (27,39.306652918458,"2018-04-07 22:03:31.531152")
NOTICE: (28,41.2743093911558,"2018-04-07 22:03:31.531152")
NOTICE: (29,79.2670299764723,"2018-04-07 22:03:31.531152")
NOTICE: (30,40.3565632645041,"2018-04-07 22:03:31.531152")
NOTICE: (31,54.4456570409238,"2018-04-07 22:03:31.531152")
NOTICE: (32,9.24196597188711,"2018-04-07 22:03:31.531152")
NOTICE: (33,33.5418107453734,"2018-04-07 22:03:31.531152")
NOTICE: (34,80.203724000603,"2018-04-07 22:03:31.531152")
NOTICE: (35,78.3704041969031,"2018-04-07 22:03:31.531152")
NOTICE: (36,68.3132612612098,"2018-04-07 22:03:31.531152")
NOTICE: (37,27.698003873229,"2018-04-07 22:03:31.531152")
NOTICE: (38,40.2470296714455,"2018-04-07 22:03:31.531152")
NOTICE: (39,65.5018238350749,"2018-04-07 22:03:31.531152")
NOTICE: (40,35.1150792557746,"2018-04-07 22:03:31.531152")
NOTICE: (41,11.110711004585,"2018-04-07 22:03:31.531152")
NOTICE: (42,99.3330503813922,"2018-04-07 22:03:31.531152")
NOTICE: (43,12.5914534088224,"2018-04-07 22:03:31.531152")
NOTICE: (44,16.2549073807895,"2018-04-07 22:03:31.531152")
NOTICE: (45,85.6422682292759,"2018-04-07 22:03:31.531152")
NOTICE: (46,53.759319614619,"2018-04-07 22:03:31.531152")
NOTICE: (47,44.0844432916492,"2018-04-07 22:03:31.531152")
NOTICE: (48,63.4803248103708,"2018-04-07 22:03:31.531152")
NOTICE: (49,67.75689185597,"2018-04-07 22:03:31.531152")
NOTICE: (50,78.9309556595981,"2018-04-07 22:03:31.531152")
NOTICE: (51,89.9745107628405,"2018-04-07 22:03:31.531152")
NOTICE: (52,56.6758279688656,"2018-04-07 22:03:31.531152")
NOTICE: (53,0.60571450740099,"2018-04-07 22:03:31.531152")
NOTICE: (54,31.3881701324135,"2018-04-07 22:03:31.531152")
NOTICE: (55,76.0515897534788,"2018-04-07 22:03:31.531152")
NOTICE: (56,54.1591947898269,"2018-04-07 22:03:31.531152")
NOTICE: (57,14.1091350931674,"2018-04-07 22:03:31.531152")
NOTICE: (58,15.3582426719368,"2018-04-07 22:03:31.531152")
NOTICE: (59,95.4335042275488,"2018-04-07 22:03:31.531152")
NOTICE: (60,93.3761650696397,"2018-04-07 22:03:31.531152")
NOTICE: (61,55.714805983007,"2018-04-07 22:03:31.531152")
NOTICE: (62,49.8791612684727,"2018-04-07 22:03:31.531152")
NOTICE: (63,2.61813104152679,"2018-04-07 22:03:31.531152")
NOTICE: (64,89.2566167283803,"2018-04-07 22:03:31.531152")
NOTICE: (65,30.0828852690756,"2018-04-07 22:03:31.531152")
NOTICE: (66,80.988535284996,"2018-04-07 22:03:31.531152")
NOTICE: (67,57.5698779895902,"2018-04-07 22:03:31.531152")
NOTICE: (68,57.7808891423047,"2018-04-07 22:03:31.531152")
NOTICE: (69,21.2355649564415,"2018-04-07 22:03:31.531152")
NOTICE: (70,23.0717018712312,"2018-04-07 22:03:31.531152")
NOTICE: (71,92.8959684446454,"2018-04-07 22:03:31.531152")
NOTICE: (72,32.3462759610265,"2018-04-07 22:03:31.531152")
NOTICE: (73,22.4047522526234,"2018-04-07 22:03:31.531152")
NOTICE: (74,5.48742185346782,"2018-04-07 22:03:31.531152")
NOTICE: (75,48.6011833418161,"2018-04-07 22:03:31.531152")
NOTICE: (76,8.04702048189938,"2018-04-07 22:03:31.531152")
NOTICE: (77,59.2467414680868,"2018-04-07 22:03:31.531152")
NOTICE: (78,92.6856266334653,"2018-04-07 22:03:31.531152")
NOTICE: (79,71.5273452922702,"2018-04-07 22:03:31.531152")
NOTICE: (80,27.003633370623,"2018-04-07 22:03:31.531152")
NOTICE: (81,71.6165823396295,"2018-04-07 22:03:31.531152")
NOTICE: (82,61.5018560551107,"2018-04-07 22:03:31.531152")
NOTICE: (83,83.6794613394886,"2018-04-07 22:03:31.531152")
NOTICE: (84,72.2222968470305,"2018-04-07 22:03:31.531152")
NOTICE: (85,92.8900261875242,"2018-04-07 22:03:31.531152")
NOTICE: (86,59.7310510929674,"2018-04-07 22:03:31.531152")
NOTICE: (87,26.3814916368574,"2018-04-07 22:03:31.531152")
NOTICE: (88,6.99916132725775,"2018-04-07 22:03:31.531152")
NOTICE: (89,75.0892938114703,"2018-04-07 22:03:31.531152")
NOTICE: (90,21.8149958644062,"2018-04-07 22:03:31.531152")
NOTICE: (91,0.375326396897435,"2018-04-07 22:03:31.531152")
NOTICE: (92,30.8040997944772,"2018-04-07 22:03:31.531152")
NOTICE: (93,71.694157179445,"2018-04-07 22:03:31.531152")
NOTICE: (94,2.99345748499036,"2018-04-07 22:03:31.531152")
NOTICE: (95,20.0607165228575,"2018-04-07 22:03:31.531152")
NOTICE: (96,1.77704244852066,"2018-04-07 22:03:31.531152")
NOTICE: (97,83.9819927699864,"2018-04-07 22:03:31.531152")
NOTICE: (98,77.6305945124477,"2018-04-07 22:03:31.531152")
NOTICE: (99,59.5579316373914,"2018-04-07 22:03:31.531152")
NOTICE: (100,5.21755772642791,"2018-04-07 22:03:31.531152")
MERGE 100
再次執行，由于設定了條件
t1.info<>t2.info or t1.crt_time<>t2.crt_time
才更新，是以MERGE 0條.
postgres=# merge into t1 using t2
on t1.id=t2.id
when not matched then insert (id,info,crt_time) values (t2.id,t2.info,t2.crt_time)
when matched and t1.info<>t2.info or t1.crt_time<>t2.crt_time then update set info=t2.info , crt_time=t2.crt_time;
MERGE 0
把條件
t1.info<>t2.info or t1.crt_time<>t2.crt_time
去掉，MERGE了100條(update)
postgres=#
merge into t1 using t2
on t1.id=t2.id
when not matched then insert (id,info,crt_time) values (t2.id,t2.info,t2.crt_time)
when matched then update set info=t2.info , crt_time=t2.crt_time;
MERGE 100
目前merge into文法與rule不能同時使用，也就是說如果目标表上有insert\update\delete的RULE的話，不能使用merge into來合并資料到目标表。
postgres=# create rule r1 as on insert to t1 do instead nothing;
CREATE RULE
postgres=# insert into t1 values (0);
INSERT 0 0
postgres=# truncate t1;
TRUNCATE TABLE
postgres=# \set VERBOSITY verbose
postgres=# merge into t1 using t2
on t1.id=t2.id
when not matched then insert (id,info,crt_time) values (t2.id,t2.info,t2.crt_time)
when matched and t1.info<>t2.info or t1.crt_time<>t2.crt_time then update set info=t2.info , crt_time=t2.crt_time;
ERROR: 0A000: MERGE is not supported for relations with rules
LOCATION: transformMergeStmt, parse_merge.c:424
![JDBC連接配接資料庫（statement）[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)