insert on conflict合并更新即存在则更新,不存在则写入。在oracle中可以通过merge语法来实现同样的功能,不过这种语法有个缺陷就是:某些情况下存在并不一定要更新,因为可能更新的数据是一样的,这就完全没必要了。在oracle中可能影响并不大,但是在pg中,由于pg是多版本的,更新会产生新的TUPLE版本,如果这种没必要的更新很多,只会给数据库带来额外的负担同时影响性能。
–创建测试表:
bill=# create table tbl(
bill(# c1 int,
bill(# c2 int,
bill(# c3 int,
bill(# c4 int,
bill(# c5 timestamp,
bill(# unique (c1,c2)
bill(# );
CREATE TABLE
–使用insert on conflict插入数据
可以发现多次插入都会更新所有记录
bill=# insert into tbl
bill-# select id,id,1,random(),now() from generate_series(1,1000) t(id)
bill-# on conflict(c1,c2)
bill-# do update
bill-# set
bill-# c3=excluded.c3,c4=excluded.c4,c5=excluded.c5;
INSERT 0 1000
bill=# insert into tbl
bill-# select id,id,1,random(),now() from generate_series(1,1000) t(id)
bill-# on conflict(c1,c2)
bill-# do update
bill-# set
bill-# c3=excluded.c3,c4=excluded.c4,c5=excluded.c5;
INSERT 0 1000
–优化写法:
加上where条件,相同的记录则不更新
bill=# insert into tbl
bill-# select id,id,1,random(),now() from generate_series(1,1000) t(id)
bill-# on conflict(c1,c2)
bill-# do update
bill-# set
bill-# c3=excluded.c3,c4=excluded.c4,c5=excluded.c5
bill-# where
bill-# tbl.c3 is distinct from excluded.c3 or
bill-# tbl.c4 is distinct from excluded.c4;
INSERT 0 500
bill=# insert into tbl
bill-# select id,id,1,random(),now() from generate_series(1,1000) t(id)
bill-# on conflict(c1,c2)
bill-# do update
bill-# set
bill-# c3=excluded.c3,c4=excluded.c4,c5=excluded.c5
bill-# where
bill-# tbl.c3 is distinct from excluded.c3 or
bill-# tbl.c4 is distinct from excluded.c4;
INSERT 0 488
可以发现只更新相关的记录。