Firebird迁移PostgreSQL：无唯一约束下实现Update or Insert方法咨询

方法1：使用WITH语句组合UPDATE和INSERT #

这种方式无需额外创建函数，通过先尝试更新目标行，再对未匹配到的行执行插入来实现逻辑。假设你的表为test_table，需要基于col1和col2的组合值判断是更新还是插入，示例代码如下：

WITH upd AS (
    UPDATE test_table 
    SET col3 = '新值'
    WHERE col1 = '目标值1' AND col2 = '目标值2'
    RETURNING *
)
INSERT INTO test_table (col1, col2, col3)
SELECT '目标值1', '目标值2', '新值'
WHERE NOT EXISTS (SELECT 1 FROM upd);

逻辑说明：

• 先执行UPDATE语句，将所有被更新的行返回至临时结果集upd
• 若upd为空（即无匹配的更新行），则执行INSERT插入新数据

方法2：用PL/pgSQL封装Upsert逻辑 #

如果需要重复使用该逻辑，可以编写PL/pgSQL函数，通过判断更新行数决定是否插入。示例如下：

CREATE OR REPLACE FUNCTION upsert_test_table(p_col1 text, p_col2 text, p_col3 text)
RETURNS void AS $$
BEGIN
    -- 尝试更新匹配的行
    UPDATE test_table 
    SET col3 = p_col3 
    WHERE col1 = p_col1 AND col2 = p_col2;
    
    -- 若未更新到任何行，执行插入
    IF NOT FOUND THEN
        INSERT INTO test_table (col1, col2, col3)
        VALUES (p_col1, p_col2, p_col3);
    END IF;
END;
$$ LANGUAGE plpgsql;

调用函数时直接传入参数即可：

SELECT upsert_test_table('目标值1', '目标值2', '新值');

注意并发竞态问题 #

由于没有唯一约束，高并发场景下可能出现多个会话同时判定“行不存在”并插入重复数据的情况。若需避免该问题，可添加行锁优化：

• 在函数中先执行SELECT 1 FROM test_table WHERE col1 = p_col1 AND col2 = p_col2 FOR UPDATE;，锁定匹配行（如果存在），确保后续更新/插入操作无冲突
• 或者将事务隔离级别设置为REPEATABLE READ，降低并发冲突概率

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