如何优化PostgreSQL中UPDATE无更新则INSERT的SQL语句？

需求逻辑（伪代码） #

records = SELECT * FROM table_1 WHERE id = x

numberOfRowsUpdated = UPDATE table_2 SET column_1 = y WHERE id = record1.id

-- 仅当查询到记录且更新行数为0时执行插入
INSERT INTO table2 (columns) VALUES (records.id, records.name, ...) WHERE records exists and numberOfRowsUpdated == 0

错误的尝试写法 #

曾尝试在WHERE子句中直接嵌入UPDATE语句，引发语法错误：

WITH records AS (SELECT id, tx_id FROM table_1 WHERE id = NEW.id)
INSERT INTO table_2 (id,txn_id)
SELECT records.id, records.txn_id)
FROM records
WHERE EXISTS (SELECT 1 FROM records)
AND (UPDATE table_2 SET has_txn = true WHERE id in (records.id) = 0)

当前可用但存在重复查询的实现 #

现有写法可正常运行，但重复查询了table_1，不够高效：

WITH records AS (SELECT * FROM table_1 WHERE txn_id = NEW.id),
numberOfRowsUpdated AS (
    UPDATE table_2 SET has_txn = true WHERE id in (SELECT id FROM 
    table_1 WHERE txn_id = NEW.id) RETURNING 1
)
INSERT INTO outbox (
 id,
 ...
) SELECT (
 records.id,
 ...
 )
FROM records, numberOfRowsUpdated
WHERE EXISTS (SELECT 1 FROM records)
AND (select count(*) from numberOfRowsUpdated) = 0;

优化后的解决方案 #

通过复用CTE中的records结果，避免重复查询table_1，优化后的语句如下：

WITH records AS (
    SELECT id, txn_id -- 明确指定所需字段，替代SELECT *
    FROM table_1 
    WHERE txn_id = NEW.id
),
numberOfRowsUpdated AS (
    UPDATE table_2 
    SET has_txn = true 
    WHERE id IN (SELECT id FROM records) -- 直接复用records的查询结果
    RETURNING 1
)
INSERT INTO outbox (
    id,
    txn_id,
    -- 填写其他需要插入的字段
) 
SELECT 
    records.id,
    records.txn_id,
    -- 对应填写其他字段
FROM records
WHERE EXISTS (SELECT 1 FROM records)
AND (SELECT count(*) FROM numberOfRowsUpdated) = 0;

优化说明 #

• 复用CTE中records的查询结果作为UPDATE的条件，彻底避免重复访问table_1
• 明确指定字段而非使用SELECT *，提升查询性能和代码可读性
• 保留原有业务逻辑：先尝试更新table_2，仅当无匹配更新行且存在table_1记录时，执行插入操作

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