PostgreSQL临时表Upsert至主表：NULL ID自动生成的单批次实现咨询

针对临时表tmp_data向主表data执行Upsert时，需保留非空ID、自动生成NULL ID，且必须单批次返回所有ID的需求，提供以下两种可行方案：

方案一：通过序列函数动态生成ID #

如果主表data的id是serial或PostgreSQL 10+的identity类型，可利用序列函数自动填充NULL的ID值，同时保留非空ID用于Upsert：

insert into data (id, name, ...)
select 
  case 
    when tmp_data.id is not null then tmp_data.id 
    else nextval(pg_get_serial_sequence('data', 'id')) 
  end as id,
  tmp_data.name,
  ... -- 填入其他需要同步的字段
from tmp_data
on conflict (id) update
set name = excluded.name,
    ... -- 填入其他需要更新的字段
returning id;

说明 #

• pg_get_serial_sequence('data', 'id')会自动获取data表id字段对应的序列名，无需手动指定序列
• nextval函数生成序列的下一个值，与数据库自动生成ID的逻辑完全一致
• 非空ID会正常触发on conflict的更新逻辑，NULL ID会生成新ID并插入

方案二：拆分数据利用DEFAULT关键字 #

将临时表数据拆分为ID非空和ID为空的两部分，分别处理后合并插入，ID为空的部分直接使用DEFAULT让数据库自动生成ID：

insert into data (id, name, ...)
select id, name, ... from tmp_data where id is not null
union all
select DEFAULT, name, ... from tmp_data where id is null
on conflict (id) update
set name = excluded.name,
    ... -- 填入其他需要更新的字段
returning id;

说明 #

• union all确保两部分数据合并为一个数据集，实现单批次插入
• ID非空的部分正常参与Upsert，ID为空的部分使用DEFAULT触发数据库自动生成ID
• 无需手动操作序列，完全依赖数据库默认规则，兼容性更好

内容的提问来源于stack exchange，提问作者Arsen Mkrtchyan