PostgreSQL 9.2无WITH子句实现Upsert语句改写求助

推荐方案：事务包裹双语句（保证原子性） #

由于PostgreSQL 9.2不支持ON CONFLICT语法，我们可以通过先尝试更新、再判断插入的逻辑，将两个操作放在同一个事务中确保原子性，完全匹配原查询的业务逻辑：

BEGIN;
-- 优先更新符合col3+col4唯一约束的行
UPDATE tbl
SET col1='123', col2='456'
WHERE col3='baz' AND col4='bat';

-- 仅当无匹配行（UPDATE未影响数据）时插入新记录
INSERT INTO tbl (col1, col2, col3, col4)
SELECT '123', '456', 'baz', 'bat'
WHERE NOT EXISTS (
    SELECT 1 FROM tbl WHERE col3='baz' AND col4='bat'
);
COMMIT;

说明 #

• 利用col3和col4的唯一约束直接定位目标行，比原查询中通过子查询找id的写法更高效
• 事务包裹确保更新和插入操作要么全部成功，要么全部回滚，避免数据不一致

单语句替代写法（仅处理插入场景，需配合单独更新） #

如果必须写成单语句，仅能实现插入逻辑（若要完成更新需单独执行UPDATE）：

INSERT INTO tbl (col1, col2, col3, col4)
SELECT '123', '456', 'baz', 'bat'
WHERE NOT EXISTS (
    SELECT 1 FROM tbl WHERE col3='baz' AND col4='bat'
);

但这种写法无法同时处理更新，需额外执行UPDATE语句才能完全匹配原逻辑，因此更推荐使用事务包裹的方案。

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