9.5以降:

PostgreSQL 9.5 以降ではINSERT ... ON CONFLICT (key) DO UPDATE(およびON CONFLICT (key) DO NOTHING)、つまり upsert がサポートされています。

比較ON DUPLICATE KEY UPDATE。

簡単な説明。

使用方法についてはマニュアル- 具体的には、構文図のconflict_action句、および説明文。

以下に示す 9.4 以前のソリューションとは異なり、この機能は複数の競合する行で動作し、排他ロックや再試行ループは必要ありません。

機能を追加するコミットはここにありますそしてその開発に関する議論はここにあります。

9.5 を使用しており、下位互換性が必要ない場合は、ここで読むのをやめてください。

9.4以前:

PostgreSQL には組み込みUPSERT(またはMERGE) 機能がないため、同時使用時に効率的に実行することは非常に困難です。

この記事ではこの問題について有益な詳細を論じている。。

一般的には、次の 2 つのオプションから選択する必要があります。

• 再試行ループ内の個々の挿入/更新操作、または
• テーブルをロックしてバッチマージを実行する

個々の行の再試行ループ

多数の接続で同時に挿入を実行しようとする場合、再試行ループで個々の行のアップサートを使用するのが適切なオプションです。

PostgreSQLのドキュメントには、データベース内のループでこれを実行できる便利な手順が記載されています。. ほとんどの単純なソリューションとは異なり、更新の損失や挿入競合を防ぎます。READ COMMITTEDただし、これは モードでのみ機能し、トランザクションで実行する唯一の操作である場合にのみ安全です。 トリガーまたはセカンダリ一意キーによって一意違反が発生する場合、この関数は正しく機能しません。

この戦略は非常に非効率的です。可能な場合は、代わりに、作業をキューに入れて、以下に説明するように一括アップサートを実行する必要があります。

この問題に対する多くの解決策はロールバックを考慮していないため、不完全な更新になります。2 つのトランザクションが競合し、一方は正常にINSERTs を実行し、もう一方は重複キー エラーが発生し、UPDATE代わりに を実行します。UPDATEブロックは、 がロールバックまたはコミットするのを待機しますINSERT。ロールバックすると、UPDATE条件の再チェックで 0 行が一致するため、UPDATEコミットしても、期待した upsert は実際には実行されません。結果の行数をチェックし、必要に応じて再試行する必要があります。

試みられた解決策の中には、SELECT 競合を考慮していないものもあります。明白で単純な方法を試してみましょう。

-- THIS IS WRONG. DO NOT COPY IT. It's an EXAMPLE.

BEGIN;

UPDATE testtable
SET somedata = 'blah'
WHERE id = 2;

-- Remember, this is WRONG. Do NOT COPY IT.

INSERT INTO testtable (id, somedata)
SELECT 2, 'blah'
WHERE NOT EXISTS (SELECT 1 FROM testtable WHERE testtable.id = 2);

COMMIT;

BEGIN;

CREATE TEMPORARY TABLE newvals(id integer, somedata text);

INSERT INTO newvals(id, somedata) VALUES (2, 'Joe'), (3, 'Alan');

LOCK TABLE testtable IN EXCLUSIVE MODE;

UPDATE testtable
SET somedata = newvals.somedata
FROM newvals
WHERE newvals.id = testtable.id;

INSERT INTO testtable
SELECT newvals.id, newvals.somedata
FROM newvals
LEFT OUTER JOIN testtable ON (testtable.id = newvals.id)
WHERE testtable.id IS NULL;

COMMIT;