☰

PostgreSQL で重複更新時に挿入しますか? 質問する

tag-icon • tag-icon postgresql upsert
PostgreSQL で重複更新時に挿入しますか? 質問する

数か月前、Stack Overflow の回答から、次の構文を使用して MySQL で一度に複数の更新を実行する方法を学びました。

INSERT INTO table (id, field, field2) VALUES (1, A, X), (2, B, Y), (3, C, Z)
ON DUPLICATE KEY UPDATE field=VALUES(Col1), field2=VALUES(Col2);

私は現在 PostgreSQL に切り替えましたが、どうやらこれは正しくないようです。正しいテーブルを参照しているので、使用されているキーワードが異なることが原因だと思いますが、PostgreSQL のドキュメントのどこにこのことが記載されているのかわかりません。

明確にするために、いくつかのものを挿入し、それらがすでに存在する場合は更新したいと思います。

ベストアンサー1

PostgreSQLバージョン9.5以降ではアップサート構文、紛争について句。次の構文を使用します（MySQLと同様）

INSERT INTO the_table (id, column_1, column_2) 
VALUES (1, 'A', 'X'), (2, 'B', 'Y'), (3, 'C', 'Z')
ON CONFLICT (id) DO UPDATE 
  SET column_1 = excluded.column_1, 
      column_2 = excluded.column_2;

postgresqlのメールグループアーカイブで「upsert」を検索すると、マニュアルであなたがやりたいことの例:

例 38-2. UPDATE/INSERT の例外

この例では、例外処理を使用して、必要に応じて UPDATE または INSERT を実行します。

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
        -- note that "a" must be unique
        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');

9.1以降のCTEを使用してこれを一括で実行する方法の例は、ハッカーのメーリングリスト:

WITH foos AS (SELECT (UNNEST(%foo[])).*)
updated as (UPDATE foo SET foo.a = foos.a ... RETURNING foo.id)
INSERT INTO foo SELECT foos.* FROM foos LEFT JOIN updated USING(id)
WHERE updated.id IS NULL;

見るa_horse_with_no_name さんの回答よりわかりやすい例です。

おすすめ記事