PostgreSQL 14中基于触发器的视图Upsert实现方案咨询

在你当前的限制（仅能访问access schema、无法修改现有结构）下，可以通过WITH语句结合更新+插入的方式实现Upsert，无需先查询再执行分开的插入/更新操作。

具体实现语句 #

WITH attempted_update AS (
    UPDATE access.data
    SET c = 4
    WHERE a = 1 AND b = 2
    RETURNING id
)
INSERT INTO access.data(a, b, c)
VALUES (1, 2, 4)
WHERE NOT EXISTS (SELECT 1 FROM attempted_update);

逻辑说明 #

1. 先尝试更新：执行UPDATE语句匹配a=1 AND b=2的行，该操作会触发视图的data_update_trigger，实际修改storage.data中的对应记录。如果存在目标行，attempted_update会返回该行的id。
2. 条件插入：只有当attempted_update为空（即更新未命中，说明目标记录不存在）时，才执行INSERT语句，触发data_insert_trigger向storage.data插入新行。

注意事项 #

• 整个操作是原子性的，所有逻辑在单个事务中完成，不会出现中间状态。
• 高并发场景下，若多个事务同时执行相同的Upsert，可能会触发storage.data的唯一约束冲突（两个事务都执行更新未命中，随后同时插入）。这种情况可以通过应用层捕获错误并重试解决，或者根据业务场景接受极低概率的冲突报错。

关于你之前的错误 #

你使用ON CONFLICT ON CONSTRAINT pair报错的原因是：pair约束属于storage.data表，而access schema无法访问该约束，PostgreSQL无法识别视图层面的这个约束名称，因此抛出错误。上述方案不需要依赖视图的约束定义，完全通过逻辑判断实现Upsert逻辑。

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