PostgreSQLでUPSERT(MERGE、INSERT ... ON DUPLICATE UPDATE)するには?
MySQLではINSERT ... ON DUPLICATE UPDATE
と呼ばれ、標準ではMERGE
操作の一部としてサポートされています。
PostgreSQLでは(pg 9.5以前では)直接サポートされていませんが、どのようにすればよいのでしょうか?以下のように考えてください:
CREATE TABLE testtable (
id integer PRIMARY KEY,
somedata text NOT NULL
);
INSERT INTO testtable (id, somedata) VALUES
(1, 'fred'),
(2, 'bob');
タプル(2, 'Joe')
, (3, 'Alan')
をupsert"したいとします:
(1, 'fred'),
(2, 'Joe'), -- Changed value of existing tuple
(3, 'Alan') -- Added new tuple
これが、upsert
について議論するときに人々が話していることである。重要なことは、どのようなアプローチであっても、明示的なロッキングを使用するか、あるいは結果として発生するレースコンディションを防御することで、同じテーブル*上で複数のトランザクションが動作していても安全でなければならないということです。
このトピックは、https://stackoverflow.com/q/1109061/398670 で広範囲に議論されていますが、MySQL 構文の代替案に関するもので、時間の経過とともに無関係な詳細がかなり増えています。確定的な答えに取り組んでいます。
これらのテクニックは、"insert if not exists, otherwise do nothing""、つまり、"insert ... on duplicate key ignore" "にも有効です。
242
6
9.5以降
PostgreSQL 9.5以降では、
INSERT ... ON CONFLICT UPDATE
(およびON CONFLICT DO NOTHING
)がサポートされています。 ON DUPLICATE KEY UPDATE`との比較。 簡単な説明。 使用法はマニュアル - 特に構文図のconflict_action節と説明テキストを参照してください。 以下に示す9.4以前の解決策とは異なり、この機能は複数の競合する行に対して動作し、排他ロックや再試行ループを必要としません。 この機能を追加したコミットはこちらで、この機能の開発に関する議論はこちらです。*もしあなたが9.5を使っていて後方互換性を必要としないのであれば、今読むのを止めても構いません。
9.4以上です:
PostgreSQLには組み込みの
UPSERT
(またはMERGE
)機能がありません。 この記事ではこの問題について詳しく説明しています。 一般的には、2つのオプションから選択しなければならない:個々の行のリトライループ
多くの接続が同時に挿入を行おうとする場合、再試行ループで個々の行の挿入を行うのが合理的です。 PostgreSQLのドキュメントには、これをデータベース内のループで実行する便利なプロシジャがあります。ほとんどの単純な解決策とは異なり、更新の失敗や挿入の競合を防ぎます。このプロシージャは
READ COMMITTED
モードでのみ動作し、トランザクション内で行うことがこのプロシージャのみである場合にのみ安全です。この関数は、トリガやセカンダリユニークキーがユニーク違反の原因となる場合、正しく動作しません。 この方法は非常に非効率的です。現実的であればいつでも、作業をキューに入れ、代わりに以下に説明するような一括アップサートを行うべきである。 この問題に対する多くの解決策はロールバックを考慮していないため、不完全な更新になってしまう。2つのトランザクションが互いに競合し、一方はINSERT
に成功し、もう一方は重複キーエラーになり、代わりにUPDATE
を行う。UPDATEは
INSERTがロールバックするかコミットするのを待ってブロックする。そのため、
UPDATE`がコミットしても、実際には期待したアップサートは行われていません。結果の行数をチェックし、必要に応じて再試行する必要がある。 また、SELECTレースを考慮していない解決策もあります。明らかで単純な方法を試すとを試した場合、2つ同時に実行するといくつかの失敗モードがあります。1つは、すでに説明した更新再チェックの問題です。もう1つは、両方が同時に
UPDATE
を実行し、0行にマッチして続行する場合です。このテストはINSERT
の前に行われます。どちらもゼロ行を得たので、どちらもINSERT
を行う。一方は重複キーエラーで失敗する。 これが、再試行ループが必要な理由である。巧妙なSQLを使えば、重複キーエラーや更新の失敗を防げると思うかもしれませんが、そうではありません。行数をチェックするか、重複キーエラーを処理して(選択したアプローチによりますが)再試行する必要があります。 このために独自のソリューションを開発しないでください。メッセージキューイングと同様、それはおそらく間違っています。ロック付き一括アップサート
新しいデータセットを古い既存のデータセットにマージするような一括アップサートを行いたいことがあります。これは個々の行のアップサートに比べて非常に効率的であり、実用的であればいつでも行うべきである。 この場合、一般的には以下のような処理を行います:
テーブルを
CREATE` する。COPY
または一括挿入する。IN EXCLUSIVE MODE
でLOCK
する。これにより、他のトランザクションはSELECT
することはできるが、テーブルに変更を加えることはできない。UPDATE ...FROM
を実行する;INSERT
を行う;COMMIT
を行い、ロックを解放する。 例えば、質問で与えられた例では、多値のINSERT
を使用して temp テーブルにデータを入力します:関連する読み物
MERGE
](http://wiki.postgresql.org/wiki/SQL_MERGE)MERGE` についてはどうですか?
SQL 標準の
MERGE
は並行性の定義が不十分で、テーブルをロックせずに UPSERT を行うには適していません。 MERGEはOLAPのデータマージには非常に便利な文ですが、並行処理で安全なアップサートを行うには役に立ちません。他のDBMSを使用している人に、アップサートに
MERGE` を使用するようにというアドバイスがたくさんありますが、実はそれは間違っています。他のDBでは
INSERT ... ON DUPLICATE KEY UPDATE
](http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html)MERGE
](http://technet.microsoft.com/en-us/library/bb510625.aspx) (ただしMERGE
の問題については上記を参照)。MERGE
from Oracle (ただしMERGE
の問題については上記を参照)PostgreSQLの9.5以前のバージョンでの単一挿入の問題に対する別の解決策を提供しようとしています。そのアイデアは、まず挿入を行い、レコードが既に存在する場合は更新を行うというものです:
この解決策は、テーブルの行の削除がない場合のみ適用できることに注意してください。
この解決策の効率については知りませんが、十分に合理的だと思われます。
insertの例をいくつか示します。 ... 紛争について。 ...
( pg 9.5 + ):-紛争時に挿入-何もしない。
ダミー(id、name、size)値(1、 'new_name'、3)に挿入します。 紛争については何もしません。
。-競合時に挿入-更新を行い、列で競合ターゲットを指定します。
ダミー(id、name、size)値(1、 'new_name'、3)に挿入します。 紛争(id)について。 設定名= 'new_name'を更新し、サイズ= 3;
を更新します。-競合時に挿入-更新を行い、制約名を介して競合ターゲットを指定します。
ダミー(id、name、size)値(1、 'new_name'、3)に挿入します。 制約dummy_pkeyの競合について。 設定名= 'new_name'を更新し、サイズ= 4;
を更新します。PostgresのSQLAlchemy upsert> = 9.5。
上記の大きな投稿はPostgresバージョンの多くの異なるSQLアプローチをカバーしているため(質問のように9.5以外)、Postgres 9.5を使用している場合は、SQLAlchemyでそれを行う方法を追加したいと思います。 独自のupsertを実装する代わりに、SQLAlchemyの機能(SQLAlchemy 1.1で追加された)を使用することもできます。 個人的には、可能であればこれらを使用することをお勧めします。 利便性のためだけでなく、PostgreSQLが発生する可能性のあるレース条件を処理できるためです。
昨日私が与えた別の回答からのクロス投稿(https://stackoverflow.com/a/44395983/2156909)。
SQLAlchemyは、
on_conflict_do_update()
とon_conflict_do_nothing()
の2つのメソッドでON CONFLICT
をサポートしています。ドキュメントからのコピー:
http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html?highlight = conflict#insert-on-conflict-upsert。
Postgresql 9.3 でテスト済み
この質問が閉じられたので、SQLAlchemyを使用してどのように実行するかについてここに投稿します。 再帰を介して、レース条件と検証エラーに対抗するために、バルクインサートまたは更新を再試行します。
まず輸入。
これで、いくつかのヘルパー機能が機能します。
そして最後にupsert関数。
使用方法は次のとおりです。
これが[
bulk_save_objects
][3]を超える利点は、挿入時に関係、エラーチェックなどを処理できることです(バルク操作とは異なります)。[3]:http://docs.sqlalchemy.org/en/latest/orm/session_api.html?highlight = bulk_save_objects#sqlalchemy.orm.session.Session.bulk_save_objects。