Идиоматический способ внедрения UPSERT в PostgreSQL

Я читал о различных реализациях UPSERT в PostgreSQL, но все эти решения относительно старые или относительно экзотические (используя записываемый CTE , например).

И я просто не эксперт psql, чтобы сразу узнать, являются ли эти решения старыми, потому что они хорошо рекомендованы или они (ну, почти все они), просто примеры игрушек, не соответствующие производственному использованию.

Каков наиболее надежный способ реализации UPSERT в PostgreSQL?

37 голосов | спросил shabunc 20 FebruaryEurope/MoscowbMon, 20 Feb 2012 19:04:58 +0400000000pmMon, 20 Feb 2012 19:04:58 +040012 2012, 19:04:58

3 ответа


19

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');
ответил Leigh Riffel 20 FebruaryEurope/MoscowbMon, 20 Feb 2012 19:38:31 +0400000000pmMon, 20 Feb 2012 19:38:31 +040012 2012, 19:38:31
26

ОБНОВЛЕНИЕ (2015-08-20):

В настоящее время существует официальная реализация для обработки upserts с помощью ON CONFLICT DO UPDATE (официальная документация). На момент написания этой статьи эта функция в настоящее время находится в 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
ответил Joshua Burns 13 J0000006Europe/Moscow 2013, 03:41:41
0

Это даст вам знать, произошла ли вставка или обновление:

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 или ошибку.

ответил John Fawcett 8 +04002013-10-08T06:16:29+04:00312013bEurope/MoscowTue, 08 Oct 2013 06:16:29 +0400 2013, 06:16:29

Похожие вопросы

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132