在Python中，PostgreSQL的ON CONFLICT DO UPDATE仅更新非空值

Question

我有一张表格，当处理记录时，我要么获得完整的记录，否则只获取要更新的列。

我想编写一个查询来处理更新，但只更新非空值的列。例如，

现有的表格：

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

输入记录：

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

我希望将第一条记录插入，并仅在第二条记录中发生“键重复冲突”时更新最后一列。我不确定如何使用execute_values方法调用编写此代码。

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)

我正在使用psycopg2执行这个命令。

- ayushgp

Answer 1

在on conflict中（特别注意do update set name = expression），您可以使用coalesce与排除的值一起更新列为指定值（如果不为空），或现有值（如果指定为空）；格式如下：

-- 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;     

- Belayer

---

为避免可能的不必要更新，请使用“... where excluded.col1 is not null or excluded.col2 is not null”语句。 - Abelisto

我已经按照这个方法很好地避免了更新已经存在的列，但是如果它们已经存在，我不希望它们被更新，但是如果该列有非空约束，则会出现psycopg2完整性错误，指出空值在列中违反了非空约束。当我运行此部分时，该字段已经不为空。我可以通过再次指定非空值来解决错误，但我宁愿不这样做。 - Tim Edwards