主页 / dba / 问题 / 13468
Accepted
shabunc
Asked: 2012-02-21 07:04:58 +0800 CST

在 PostgreSQL 中实现 UPSERT 的惯用方式

  • 772

我已经阅读了UPSERTPostgreSQL 中的不同实现，但所有这些解决方案都相对较旧或相对陌生（例如，使用可写 CTE）。

而且我根本不是 psql 专家，无法立即找出这些解决方案是否过时，因为它们受到了很好的推荐，或者它们（嗯，几乎所有这些都是）只是不适合生产使用的玩具示例。

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

postgresql plpgsql

3 个回答

  1. 更新（2015-08-20）：

    现在有一个通过使用ON CONFLICT DO UPDATE（官方文档）来处理 upsert 的官方实现。在撰写本文时，此功能当前位于 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是将值合并到单个资源中，而不是必须指定 price 和 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
    • 28
  2. Best Answer

    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');
    • 24

  3. 这将让您知道插入或更新是否发生：

    with "update_items" as (
  -- Update statement here
  update items set price = 3499, name = 'Uncle Bob'
  where id = 1 returning *
)
-- Insert statement here
insert into items (price, name)
-- But make sure you put your values like so
select 3499, 'Uncle Bob'
where not exists ( select * from "update_items" );

    如果发生更新，您将得到一个插入 0，否则插入 1 或错误。

    • 0

相关问题