PgSQL upsert批量查询插入或更新(insert select/on conflict do update踩坑记录)

insert into t --进行插入
values(1,'name') 
ON CONFLICT(id) --如果id这个键存在
do update set --更新以下字段
name=EXCLUDED.name ;


insert into t (a1,b1,c1)
select a2,b2,c2
from t2 
on conflict(a1) 
do update set 
(b1,c1) = (1,2)

conflict里的字段必须为主键或者唯一索引，可以多个字段作为唯一索引，在数据库设置唯一，不然会报

"there is no unique or exclusion constraint matching the ON CONFLICT specification"错误

 

批量查询插入的时候想到了update时再按上面selete查询一遍应该就能自动全插进去了吧,然而现实总是残酷的

insert into t (a1,b1,c1)
        select a2,b2,c2
        from t2 
        on conflict(a1) do update set 
        (b1,c1)= (select a2,b2,c2 from t2)

报错:"more than one row returned by a subquery used as an expression"

这样的查询 do update set只能设置一条数据

插入和更新并不是批量数据一一对应操作的 而是批量insert然后再判断是否更新 所以下面只能写死一条数据 多了就报错..遂百度

发现pgsql在这种情况下会提供一个EXCLUDED临时表把之前插入的值储存起来，然后就可以

insert into t (a1,b1,c1)--进行插入操作
        select a2,b2,c2
        from t2 
        on conflict(a1) --如果存在a1
do update set --进行下面字段更新
        (b1,c1) = (EXCLUDED.b1,EXCLUDED.c1)

这样就能实现批量查询插入或更新了