如何确保PostgreSQL中先执行DELETE再执行INSERT避免主键冲突？

你遇到的核心问题是PostgreSQL的CTE子查询是并行执行的，不是按照你书写的顺序依次运行。这就导致你的DELETE和INSERT操作可能同时对variable_page_category表进行读写，当INSERT尝试插入数据时，DELETE还没完成旧数据的清理，自然就触发了主键重复的约束错误。

下面给你两种可行的解决方案，优先推荐第二种，更直观易懂：

方案一：通过CTE依赖强制执行顺序 #

让INSERT的CTE显式依赖DELETE的CTE结果，这样PostgreSQL会先完成DELETE操作，再执行INSERT。修改后的SQL如下：

WITH result_variable_page_category_delete AS (
 DELETE FROM common.variable_page_category 
 WHERE variable_id = (dynamic_variable_json->>'id')::BIGINT 
 RETURNING 1
),
result_variable_page_category AS (
 INSERT INTO common.variable_page_category (page_category_id, variable_id)
 SELECT (pc_id::TEXT)::BIGINT, (dynamic_variable_json->>'id')::BIGINT
 FROM jsonb_array_elements_text((dynamic_variable_json->>'page_category_id')::JSONB) AS pc_id,
      result_variable_page_category_delete -- 引入DELETE的结果，建立依赖关系
 RETURNING 1
)
SELECT 1; -- 必须有主查询触发CTE的执行

这里通过在INSERT的SELECT语句中加入result_variable_page_category_delete，让数据库知道必须先完成DELETE才能获取到它的结果，从而强制执行顺序。

方案二：使用事务包裹独立的DELETE和INSERT语句 #

如果不需要用CTE的返回结果，直接将两个语句放在一个事务里执行是更简单的方式，事务中的语句会严格按顺序执行：

BEGIN;
-- 先执行DELETE清理旧数据
DELETE FROM common.variable_page_category 
WHERE variable_id = (dynamic_variable_json->>'id')::BIGINT;

-- 再执行INSERT插入新数据
INSERT INTO common.variable_page_category (page_category_id, variable_id)
SELECT (page_category_id::TEXT)::BIGINT, (dynamic_variable_json->>'id')::BIGINT
FROM jsonb_array_elements_text((dynamic_variable_json->>'page_category_id')::JSONB) AS page_category_id;
COMMIT;

这个方案逻辑清晰，完全避免了并行执行带来的冲突，而且调试起来也更方便。如果是在PL/pgSQL函数中使用，甚至可以不用手动写BEGIN和COMMIT，因为函数默认会在一个事务中执行所有语句。

另外，你提到UPDATE是可选方案，但对于这种“先清后加”的场景，DELETE+INSERT的组合其实比UPDATE更合适，尤其是当关联的page_category_id集合可能完全变化的时候，不需要去判断哪些要删哪些要加，逻辑更简单。

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