PostgreSQL：如何在带唯一约束的两张表中使用同一ID实现Upsert操作

首先得修正两个关键基础问题：你的tag表缺少实现唯一约束的必要配置，tag_hotcolumns表也缺少关联tag的字段——这俩是完成你需求的前提。

第一步：完善表结构 #

1. 给tag表添加唯一约束，确保key和value的组合唯一（这是你处理冲突的核心依据）：

ALTER TABLE tag ADD CONSTRAINT tag_key_value_unique UNIQUE (key, value);

2. 给tag_hotcolumns表添加关联tag的字段（不然没法用tag的ID关联并更新热字段）：

ALTER TABLE tag_hotcolumns ADD COLUMN tag_id bigint REFERENCES tag(id);
-- 如果需要确保每个tag只对应一条热字段记录，再加个唯一约束
ALTER TABLE tag_hotcolumns ADD CONSTRAINT tag_hotcolumns_tag_id_unique UNIQUE (tag_id);

第二步：修正SQL语句 #

你之前的错误根源是：PostgreSQL不允许在INSERT的子查询内部使用包含数据修改操作（比如INSERT）的CTE，这类带写操作的CTE必须放在整个SQL语句的顶层。

以下是匹配你需求的两种正确写法：

场景1：仅插入热字段记录（tag存在/插入后，新增对应的热字段条目） #

WITH s AS (
    -- 查询已存在的tag ID
    SELECT id FROM tag WHERE key = 'key1' AND value = 'value1'
), i AS (
    -- 如果tag不存在则插入，返回新生成的ID
    INSERT INTO tag (key, value)
    SELECT 'key1', 'value1' WHERE NOT EXISTS (SELECT 1 FROM s)
    RETURNING id
), tag_id AS (
    -- 合并插入和查询得到的所有tag ID
    SELECT id FROM i UNION ALL SELECT id FROM s
)
-- 用拿到的tag ID插入热字段表
INSERT INTO tag_hotcolumns (tag_id, hot, stuff)
SELECT id, 'hot1', 'stuff1' FROM tag_id;

场景2：存在则更新，不存在则插入（符合你说的“唯一约束冲突时更新热字段”） #

如果tag_hotcolumns里已有对应tag的记录，就更新热字段；没有则插入，用ON CONFLICT实现：

WITH s AS (
    SELECT id FROM tag WHERE key = 'key1' AND value = 'value1'
), i AS (
    INSERT INTO tag (key, value)
    SELECT 'key1', 'value1' WHERE NOT EXISTS (SELECT 1 FROM s)
    RETURNING id
), tag_id AS (
    SELECT id FROM i UNION ALL SELECT id FROM s
)
INSERT INTO tag_hotcolumns (tag_id, hot, stuff)
SELECT id, 'hot1', 'stuff1' FROM tag_id
ON CONFLICT (tag_id) DO UPDATE 
SET hot = EXCLUDED.hot, stuff = EXCLUDED.stuff;

错误原因说明 #

你之前把带INSERT操作的CTE嵌套在了INSERT INTO (...)的括号里，而PostgreSQL明确规定：包含数据修改语句（INSERT/UPDATE/DELETE）的CTE必须是顶层CTE，不能作为子查询的一部分，所以才会触发"WITH clause containing a data-modifying statement must be at the top level"的报错。

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