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)
           

這樣就能實作批量查詢插入或更新了