PostgreSQL中ON CONFLICT时向关联表插入日志的语法问题

你遇到的语法错误是因为PostgreSQL的ON CONFLICT语法规则限制：DO关键字后仅允许跟UPDATE或NOTHING，不能直接嵌套INSERT语句。

根据批量插入并追踪冲突的需求，推荐两种实现方式：

方式一：使用CTE（公共表表达式）捕获冲突并插入日志 #

这种方式无需额外创建函数，逻辑直观，适合单条或批量插入场景：

单条插入场景 #

WITH attempted_insert AS (
    INSERT INTO fiche (content) 
    VALUES ('toto')
    ON CONFLICT (content) DO NOTHING
    RETURNING content
)
INSERT INTO fiche_qualites (poi_id, qualites_id)
SELECT idPoi, 'Code error'
WHERE NOT EXISTS (SELECT 1 FROM attempted_insert);

逻辑说明：先尝试向fiche插入数据，冲突则跳过；若attempted_insert无返回结果（说明插入冲突），则向日志表插入记录。

批量插入场景 #

如果是循环批量插入多条数据，可以调整为批量处理，自动捕获所有冲突行：

-- 假设待插入的content列表为('toto', 'tata', 'toto')，对应的poi_id为(1,2,1)
WITH input_data AS (
    SELECT unnest(ARRAY['toto', 'tata', 'toto']) AS content,
           unnest(ARRAY[1, 2, 1]) AS poi_id
),
attempted_inserts AS (
    INSERT INTO fiche (content)
    SELECT content FROM input_data
    ON CONFLICT (content) DO NOTHING
    RETURNING content
)
INSERT INTO fiche_qualites (poi_id, qualites_id)
SELECT poi_id, 'Code error'
FROM input_data
WHERE content NOT IN (SELECT content FROM attempted_inserts);

逻辑说明：先定义待插入的数据集，尝试批量插入fiche，最后筛选出插入失败（冲突）的行，批量插入日志。

方式二：使用函数+ON CONFLICT DO UPDATE #

如果需要更灵活的冲突处理逻辑，可以创建一个辅助函数，在冲突时调用它插入日志：

1. 先创建日志插入函数：

CREATE OR REPLACE FUNCTION log_fiche_duplicate(p_poi_id INT, p_content TEXT)
RETURNS TEXT AS $$
BEGIN
    INSERT INTO fiche_qualites (poi_id, qualites_id)
    VALUES (p_poi_id, 'Code error: 重复内容 ' || p_content);
    RETURN p_content; -- 返回原内容，确保更新后数据无变化
END;
$$ LANGUAGE plpgsql;

2. 执行插入时调用函数：

INSERT INTO fiche (content)
VALUES ('toto')
ON CONFLICT (content) DO UPDATE
SET content = log_fiche_duplicate(idPoi, 'toto');

逻辑说明：冲突触发时执行一个空更新（仅调用函数插入日志，不修改原数据）。

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