In the 关于冲突 https://www.postgresql.org/docs/current/sql-insert.html(特别注意更新集合名称=表达式)您可以使用合并与排除的值将列更新为指定值(如果不为空)或现有值(如果指定为空);
这将是这样的格式:
-- 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;