我已经阅读了PostgreSQL中不同的UPSERT实现，但是所有这些解决方案都相对较旧或相对陌生（例如，使用可写CTE）。立即发现这些解决方案是否过时，因为它们是值得推荐的，还是（几乎全部都是）这些玩具示例不适合生产用途。

在PostgreSQL中实现UPSERT的最线程安全的方法是什么？

#1 楼

PostgreSQL现在具有UPSERT。


目前，基于类似StackOverflow问题的首选方法是：

CREATE TABLE db (a INT PRIMARY KEY, b TEXT);

CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS
$$
BEGIN
    LOOP
        -- first try to update the key
        UPDATE db SET b = data WHERE a = key;
        IF found THEN
            RETURN;
        END IF;
        -- not there, so try to insert the key
        -- if someone else inserts the same key concurrently,
        -- we could get a unique-key failure
        BEGIN
            INSERT INTO db(a,b) VALUES (key, data);
            RETURN;
        EXCEPTION WHEN unique_violation THEN
            -- do nothing, and loop to try the UPDATE again
        END;
    END LOOP;
END;
$$
LANGUAGE plpgsql;

SELECT merge_db(1, 'david');
SELECT merge_db(1, 'dennis');


评论

我宁愿使用可写的CTE：stackoverflow.com/a/8702291/330315

– a_horse_with_no_name
2012-2-21在11:33


可写CTE与函数相比有什么优势？

–FrançoisBeausoleil
2012-02-21 13:23


@François一件事，速度。使用CTE，您只需打一次数据库。这样，您可能会击中两次或更多次。而且，优化器无法像纯SQL代码一样高效地优化pl / pgsql过程。

–亚当·麦克勒（Adam Mackler）
13年11月23日在11:10
@François再说一遍，并发。由于上面的示例包含多个SQL语句，因此您必须担心竞争条件（klugey循环的原因）。一条SQL语句将是原子的。看到这个链接

–亚当·麦克勒（Adam Mackler）
13年11月23日在11:16


@FrançoisBeausoleil为何在这里和这里看到。基本上没有重试循环，您要么必须序列化，要么由于固有的竞争条件而可能失败。

–杰克·道格拉斯（Jack Douglas）
2014年4月7日下午5:51

#2 楼

更新（2015-08-20）：

现在有了使用ON CONFLICT DO UPDATE（官方文档）来处理upserts的官方实现。在撰写本文时，此功能当前位于PostgreSQL 9.5 Alpha 2中，可以从以下位置下载：Postgres源目录。这里是一个示例，假设item_id是您的主键：

INSERT INTO my_table
    (item_id, price)
VALUES
    (123456, 10.99)
ON
    CONFLICT (item_id)
DO UPDATE SET
    price = EXCLUDED.price


原始文章...

这是我希望了解是否发生插入或更新的实现。

upsert_data的定义是将值合并为一个资源，而不必两次指定价格和item_id：一次用于更新，一次用于插入。

WITH upsert_data AS (
    SELECT
    '19.99'::numeric(10,2) AS price,
    'abcdefg'::character varying AS item_id
),
update_outcome AS (
    UPDATE pricing_tbl
    SET price = upsert_data.price
    FROM upsert_data
    WHERE pricing_tbl.item_id = upsert_data.item_id
    RETURNING 'update'::text AS action, item_id
),
insert_outcome AS (
    INSERT INTO
        pricing_tbl
    (price, item_id)
    SELECT
        upsert_data.price AS price,
        upsert_data.item_id AS item_id
    FROM upsert_data
    WHERE NOT EXISTS (SELECT item_id FROM update_outcome LIMIT 1)
    RETURNING 'insert'::text AS action, item_id
)
SELECT * FROM update_outcome UNION ALL SELECT * FROM insert_outcome


如果您不喜欢使用upsert_data，请使用以下替代实现：

WITH update_outcome AS (
    UPDATE pricing_tbl
    SET price = '19.99'
    WHERE pricing_tbl.item_id = 'abcdefg'
    RETURNING 'update'::text AS action, item_id
),
insert_outcome AS (
    INSERT INTO
        pricing_tbl
    (price, item_id)
    SELECT
        '19.99' AS price,
        'abcdefg' AS item_id
    WHERE NOT EXISTS (SELECT item_id FROM update_outcome LIMIT 1)
    RETURNING 'insert'::text AS action, item_id
)
SELECT * FROM update_outcome UNION ALL SELECT * FROM insert_outcome


评论

效果如何？

– jb。
2013年12月10日17:54
@jb。不如我想要的。与执行直接插入相比，您将看到明显的性能损失。但是，对于较小的批次（例如1000或更少），此示例应该可以正常运行。

–约书亚伯恩斯
2014年1月22日17:12

#3 楼

这将使您知道是否发生了插入或更新： br />