PostgreSQL 10：使用此upsert函数，仅当列值不同时如何更新？ [英] PostgreSQL 10: With this upsert function, how to update only if column value is different?

问题描述

我只想更新 apr ，现在，无论是否不同，它看起来都在更新：

I'm looking to update apr only if it's different, right now, it looks like it updates regardless if it's different or same:

   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;

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

How can this query be changed to only update if value is different?

推荐答案

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

You can use a WHERE clause on the update:

 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;

这篇关于PostgreSQL 10：使用此upsert函数，仅当列值不同时如何更新？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！