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.

Answer 1

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;