PostgreSQL：能否用单条语句实现跨表Upsert（插入或更新）？

当然可以！PostgreSQL从9.5版本开始就支持UPSERT（插入或更新二合一）操作，对应的语法是INSERT ... ON CONFLICT ... DO UPDATE，完美替代你原来分开写的UPDATE和INSERT两条语句，还能保证操作的原子性（要么全部执行成功，要么全部回滚，避免数据不一致）。

改写后的完整代码 #

结合你原来的临时表导入逻辑，单条语句实现的方案如下：

CREATE TEMP TABLE IF NOT EXISTS tmp_table_a (LIKE table_a INCLUDING DEFAULTS);
COPY tmp_table_a FROM STDIN DELIMITER ',' CSV;

-- 单条UPSERT语句替代原有的UPDATE+INSERT
INSERT INTO table_a (id, column1, column2)
SELECT tmp.id, tmp.column1, tmp.column2 FROM tmp_table_a tmp
ON CONFLICT (id) DO UPDATE 
SET 
  column1 = EXCLUDED.column1,
  column2 = EXCLUDED.column2;

关键语法说明 #

• ON CONFLICT (id)：指定触发冲突的列（这里是id），前提是table_a的id列必须有主键约束或唯一索引，否则PostgreSQL无法识别“重复插入”的冲突场景。
• EXCLUDED：这个特殊表代表原本要插入但因为冲突被拦截的那条数据，用它来引用临时表中对应的字段值，实现更新操作。
• 如果需要更新更多字段，只需在SET后面继续添加类似column3 = EXCLUDED.column3的语句即可。

对比原有方案的优势 #

1. 原子性：单条语句是一个原子操作，不会出现“更新了部分行，插入时出错导致数据半更新”的情况。
2. 简洁性：减少了SQL语句的数量，逻辑更集中，便于维护。
3. 性能优化：避免了两次扫描临时表和目标表的开销，执行效率更高。

内容的提问来源于stack exchange，提问作者Mohamed Habib