Merge

名称

合并算子

介绍

Merge算子用于将源表中的数据行以更新或插入的方式合并到目标表中。

举例

--清理环境
DROP TABLE t1 CASCADE;
DROP TABLE t2 CASCADE;

--创建环境
CREATE TABLE t1(a int,b int);
INSERT INTO t1 SELECT generate_series(1,3),generate_series(11,13);
INSERT INTO t1 VALUES(6,16);
ANALYZE t1;

CREATE TABLE t2(a int,b int);
INSERT INTO t2 SELECT generate_series(1,5),generate_series(21,25);
ANALYZE t2;

EXPLAIN MERGE INTO t2 USING t1 ON (t1.a = t2.a)
WHEN MATCHED THEN
    UPDATE SET t2.b = t1.b
    DELETE WHERE t2.a = 3
WHEN NOT MATCHED THEN
    INSERT VALUES(t1.a , t1.b) WHERE t1.a = 6;
                                 QUERY PLAN
----------------------------------------------------------------------------
 Merge  (cost=14.25..14.26 rows=4 width=24)
   ->  Sort  (cost=14.25..14.26 rows=4 width=24)
         Sort Key: "ROWID"
         ->  Hash Join(Left Join)  (cost=7.11..14.21 rows=4 width=24)
               Hash Cond: ("outer".A = "inner".A)
               ->  Seq Scan on T1  (cost=0.00..7.04 rows=4 width=8)
               ->  Hash  (cost=7.05..7.05 rows=5 width=16)
                     ->  Seq Scan on T2  (cost=0.00..7.05 rows=5 width=16)
(8 rows)