PostgreSQL 10 : With this upsert function, 如何仅在列值不同时更新？

Question

我希望仅在 apr 不同时更新它，现在，无论它是不同还是相同，它看起来都在更新:

   INSERT INTO live_mytable (id, loan_type, apr, term, oldestyear) 
   SELECT id, loan_type, apr, term, oldestyear
   FROM   imp_mytable 
   ON CONFLICT (id,loan_type,term,oldestyear) DO update
   set apr = excluded.apr;

如何将此查询更改为仅在值不同时更新？

Answer 1

您可以在更新中使用 WHERE 子句:

 INSERT INTO live_mytable (id, loan_type, apr, term, oldestyear) 
   SELECT id, loan_type, apr, term, oldestyear
   FROM   imp_mytable 
 ON CONFLICT (id,loan_type,term,oldestyear) 
 DO update
   set apr = excluded.apr
 WHERE 
      live_mytable.apr IS DISTINCT FROM 
      EXCLUDED.apr;