PostgreSQL 10: With this upsert function, how to update only if column value is different?

Question

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?

JGH · Accepted Answer · 2019-02-28 19:04:01Z

3

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;

answered Feb 28, 2019 at 19:04

JGH

18.2k5 gold badges40 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL 10: With this upsert function, how to update only if column value is different?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related