Postgres ON CONFLICT DO UPDATE only non null values in python

Question

I have a table for which when processing records I get either the full record, else only the columns to be updated.

I want to write a query to handle updates but only update the columns with non null values. For example,

Existing table:

1 | John Doe | USA
2 | Jane Doe | UK

Incoming records:

(3, Kate Bill, Canada)
(2, null, USA)

I want to insert the first record and on conflict of key on second record ONLY update the last column.

I'm not sure how to write this using a execute_values method call:

execute_values(cursor, "INSERT INTO user_data\
           (id, name, country) VALUES %s ON CONFLICT DO UPDATE SET \
            <how to only set non null values here>", vendor_records)

I'm using psycopg2 to execute this.

Belayer · Accepted Answer · 2020-10-08 18:30:56Z

12

In the on conflict (pay particular attention to the do update set name = expression) you can use coalesce with the excluded values to update columns to the specified values (if not null), or the existing values (if specified is null); This would be that format:

-- setup  
 create table test1(id  integer primary key, col1 text,col2 text); 
 insert into test1(id, col1, col2)
    values (1, 'John Doe', 'USA')
         , (2, 'Jane Doe', 'UK');
 select * from test1; 

 -- test on conflict
 insert into test1 as t(id, col1, col2)     
      values (3, 'Kate Bill', 'Canada')
           , (2, null, 'USA')
    on conflict(id) do update   
       set col1 = coalesce(excluded.col1,  t.col1)
         , col2 = coalesce(excluded.col2,  t.col2);

-- validate
 select * from test1;

edited Oct 8, 2020 at 18:30

answered Apr 29, 2020 at 22:12

Belayer

15.1k2 gold badges13 silver badges26 bronze badges

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

2 Comments

Abelisto Over a year ago

... where excluded.col1 is not null or excluded.col2 is not null to avoid possible unnecessary updates.

Tim Edwards Over a year ago

I've followed this nicely to help with not updating columns which were there already but if they were there already, I don't want them to be updated but the column has a not null constraint then I get a psycopg2 integrityerror of null value in column violates not-null constraint. This field is already not null when I'm running this part. I can get by the error by specifying the not-null values again but I'd rather not need to do this.

Collectives™ on Stack Overflow

Postgres ON CONFLICT DO UPDATE only non null values in python

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related