PostgreSQL 13中CTE插入查询部分场景无返回结果求助

问题根源 #

你的查询在部分场景下无结果返回，核心原因是：当INSERT ... ON CONFLICT DO UPDATE触发唯一约束冲突，但DO UPDATE的WHERE条件（records.color IS NOT NULL OR records.shape <> 'NONE'）不满足时，RETURNING *不会返回任何行，此时inserted CTE为空。如果后续UNION的第二个SELECT语句因参数绑定错误、事务隔离级别等意外情况未命中目标行，就会导致整个查询无输出。

修复方案 #

最可靠的写法是放弃依赖RETURNING的结果，直接在CTE执行插入/更新操作后，查询目标行。这样无论插入成功、冲突后更新执行还是冲突后未更新，都能确保返回目标行：

WITH attempted AS (
    INSERT INTO records 
            (field1, field2, field3)
    VALUES 
            (?, ?, ?)
    ON CONFLICT ON CONSTRAINT records_field1_field2_field3_pk
    DO UPDATE
    SET color = NULL, shape = 'NONE' 
    WHERE records.color IS NOT NULL OR records.shape <> 'NONE'
)
SELECT
    records.id              AS id,
    records.field1          AS field1,
    records.color           AS color,
    backup.id               AS backup_id
FROM records
LEFT JOIN backup ON records.user_id = backup.id
WHERE records.field1 = ? AND records.field2 = ? AND records.field3 = ?
ORDER BY color DESC
LIMIT 1;

关键优化点 #

1. 简化逻辑：不再依赖RETURNING的结果，直接操作后查询目标行，避免CTE为空导致的无结果问题。
2. 确保唯一性：LIMIT 1保证始终只返回一行（符合你的需求），结合唯一约束，目标行只会存在一条。
3. 参数一致性：务必保证INSERT和后续WHERE中的三个?参数顺序、值完全一致，否则会出现查询不到的情况。

原写法的其他问题 #

• 原SQL中第一个SELECT的backup_id字段后多了一个逗号（backup.id AS backup_id,），这会导致语法错误，需要删除。
• 使用UNION会自动去重，若inserted和records查询返回相同行，会合并，但效率不如UNION ALL；而新写法直接避免了这种冗余。

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