PostgreSQL执行INSERT冲突时更新另一张表语法错误如何解决

报错根因 #

PostgreSQL 的 INSERT ... ON CONFLICT DO UPDATE 语法限定 DO UPDATE 仅可更新当前执行插入的目标表（即示例中的test01表），不支持直接更新其他表，因此你写的更新result表的语句会触发语法错误。

实现方案 #

方案1：单条CTE SQL实现（适合单次/代码内调用场景） #

通过通用表表达式判断插入是否触发冲突，冲突则执行result表更新，无需额外创建数据库对象：

WITH insert_attempt AS (
    INSERT INTO test01 (col1, col2)
    SELECT col1, col2 FROM (SELECT 1 col1,'test' col2) AS temp
    ON CONFLICT (col1) DO NOTHING
    RETURNING 1
)
UPDATE result AS rst 
SET conflictid = excluded.col1, updated_at = now() 
FROM (SELECT 1 col1) AS excluded
WHERE rst.conflictid = excluded.col1
AND NOT EXISTS (SELECT 1 FROM insert_attempt);

方案2：触发器实现（适合全局生效场景） #

如果所有test01表的插入冲突都需要自动同步更新result表，可以通过触发器实现：

1. 创建触发函数

CREATE OR REPLACE FUNCTION sync_result_on_test01_conflict()
RETURNS TRIGGER AS $$
BEGIN
  UPDATE result 
  SET conflictid = NEW.col1, updated_at = NOW()
  WHERE conflictid = NEW.col1;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

2. 绑定触发器到test01表

CREATE TRIGGER trg_test01_conflict_sync_result
AFTER INSERT OR UPDATE ON test01
FOR EACH ROW
WHEN (pg_trigger_depth() = 0)
EXECUTE FUNCTION sync_result_on_test01_conflict();

-- 插入语句调整为冲突时执行空更新，触发触发器逻辑
INSERT INTO test01 (col1, col2) 
VALUES (1, 'test')
ON CONFLICT (col1) DO UPDATE SET col1 = EXCLUDED.col1;

内容的提问来源于stack exchange，提问作者kaviya .P