sql - Postgresql - 在违反唯一约束时更新行或删除

Question

我有一个多对多表“permit_role”，如下所示:

<表类="s-表"><头>permit_idrole_id<正文>允许_1角色_1允许_2角色_1允许_3角色_3允许_4角色_1允许_4角色_2

---

它有一个唯一的约束 (permit_id, role_id) .

我需要按如下方式更新许可证:

permit_1 --> permit_5

permit_2 --> permit_5

所以不可能只做

UPDATE permit_role SET permit_id = 'permit_5' WHERE permit_id = 'permit_1';

UPDATE permit_role SET permit_id = 'permit_5' WHERE permit_id = 'permit_2';

因为我将违反唯一约束(两行带有 (permit_5, role_1))。在这种情况下，我只需要删除重复的行，所以最后只有一个这样的行。

我考虑过临时表，但似乎很难，请告知实现我的目标最简单的方法是什么。

Answer 1

尝试 CTE:

WITH old_rows AS (
   DELETE FROM permit_role
   WHERE permit_id = 'permit_1'
   RETURNING role_id
)
INSERT INTO permit_role (permit_id, role_id)
SELECT 'permit_5', role_id
FROM old_rows
ON CONFLICT (permit_id, role_id) DO NOTHING;

然后为 permit_2 重复。 ON CONFLICT 子句将防止您插入重复项而不会出错。