在R中实现PostgreSQL表的UPSERT：新增数据跳过重复主键

下面提供两种实用方案，适配你当前的R+PostgreSQL环境：

方案一：R端筛选后插入（适合小规模数据） #

先从数据库获取已存在的ID，过滤新数据后再插入：

# 从数据库读取所有已存在的ID
existing_ids <- tbl(con, "my_table") %>% 
  select(id) %>% 
  collect() %>% 
  pull(id)

# 筛选新数据中ID不存在于数据库的行
df_to_insert <- df_new %>% 
  filter(!id %in% existing_ids)

# 仅当有需要插入的数据时执行操作
if (nrow(df_to_insert) > 0) {
  dbAppendTable(con, "my_table", df_to_insert)
}

方案二：PostgreSQL原生冲突处理（适合大规模数据，效率更高） #

利用PostgreSQL的ON CONFLICT DO NOTHING语法，直接在数据库端处理冲突，无需将大量ID拉到R中：

第一步：确保ID列为主键（若未设置） #

# 给my_table的id列添加主键约束（仅需执行一次）
dbExecute(con, "ALTER TABLE my_table ADD PRIMARY KEY (id);")

第二步：执行带冲突处理的插入 #

方式A：直接构造INSERT语句（小数据）

# 生成包含冲突处理的INSERT语句
insert_sql <- glue::glue_sql(
  "INSERT INTO my_table (id, colA, colB, colC, colD)
   VALUES {vals}
   ON CONFLICT (id) DO NOTHING;",
  vals = glue::glue_data(df_new, "({id}, {colA}, {colB}, '{colC}', {colD})", .sep = ", "),
  .con = con
)

# 执行插入
dbExecute(con, insert_sql)

方式B：临时表批量插入（大数据）

这种方式避免生成超长SQL语句，适合数据量较大的场景：

# 创建临时表存储新数据（会话结束后自动删除）
dbWriteTable(con, "temp_new_data", df_new, row.names = FALSE, temporary = TRUE)

# 从临时表插入到主表，冲突则跳过
dbExecute(con, "
  INSERT INTO my_table (id, colA, colB, colC, colD)
  SELECT id, colA, colB, colC, colD FROM temp_new_data
  ON CONFLICT (id) DO NOTHING;
")

执行任意一种方案后，你可以验证结果：

desired_df <- tbl(con, "my_table") %>% collect()
print(desired_df)

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