PostgreSQLでUPSERTを実装するための一般的な方法

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"); 

コメント

• I ' dはむしろ書き込み可能なCTEを使用します： stackoverflow.com/a/8702291/330315
• What '書き込み可能なCTEと関数の利点は何ですか？
• @Fran ç 1つは、速度です。 CTEを使用して、データベースに1回アクセスします。このようにすると、2回以上ヒットする可能性があります。また、オプティマイザは'純粋なSQLコードと同じくらい効率的にpl / pgsqlプロシージャを最適化できません。
• @Fran ç ois別のこととして、並行性。上記の例には複数のSQLステートメントがあるため、競合状態（クルージーループの理由）について心配する必要があります。単一のSQLステートメントはアトミックになります。 このリンク
• @Fran ç oisBeausoleilを参照こことここの理由。基本的に再試行ループがない場合は、シリアル化する必要があるか、固有の競合状態が原因で失敗する可能性があります。

UPDATE （2015-08-20）：

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を2回指定するのではなく、値を1つのリソースにまとめます。1回は更新用、もう1回は挿入用です。

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 

コメント

• どのように機能しますか？
• @jb。私が望むほどではありません。'が表示されます重要なパフォーマンスペナルティies対ストレートインサートの実行。ただし、小さいバッチ（たとえば、1000以下）の場合、この例は問題なく実行されます。

これ挿入または更新が発生したかどうかが通知されます。

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またはエラーが発生します。