仕事でお客様からデータをEXCELとかで預かって、データベースに投入する時に割と苦労しませんか?
受領したEXCELファイルのフォーマットはバラバラ、データ内容に不具合ありーの。。。。
そんな時は、正しいデータだけを先に入れて後でその差分をゆっくり直したいですよね。
今回はデータベースにPostgreSQLを使っている人向けのTIPSです。
PostgreSQLのバージョン【9.5】以上は、「ON CONFLICT 」オプションが使えるようになっています。
このオプションは、インサートする時に同じ既存データがあった時にエラーを出すのでなく新規データだけを処理します。
例)100件のデータをtesttableに入れる時に、既に5件の既存データが存在した場合
95件のみインサートしてくれます。
書式と文例は以下の通り。
今回は佐藤さんのデータが既にtesttable内に存在していたと言う事にします。
insert into testtable (id,name,tel) values (1,’山田’,’03-3345-9999′) ON CONFLICT DO NOTHING;
insert into testtable (id,name,tel) values (2,’佐藤’,’03-3345-8888′) ON CONFLICT DO NOTHING;
※今回投入するデータは「ID」、「氏名」、「電話番号」と仮定しました。
これを実行すると山田さんのデータがだけがインサートされて、何もエラー表示されません。
こんなデータが数千件以上あった場合に非常に重宝します。
実際に数千から数百万のデータを処理する時にはトランザクションを組み合わせます。
例)以下のようなSQLファイル(test.sql)を作成します。
BEGIN;
insert into testtable (id,name,tel) values (1,’山田’,’03-3345-9999′) ON CONFLICT DO NOTHING;
insert into testtable (id,name,tel) values (2,’佐藤’,’03-3345-8888′) ON CONFLICT DO NOTHING;
COMMIT;
SSHでログインして、コマンドで上記ファイルを実行します。(PostgreSQL11の場合)
>/usr/pgsql-11/bin/psql -U postgres -d testdb < /home/postgres/test.sql
phpPgadminなんかで実行するよりも数百倍速いです。
コメント(0)
コメントはまだありません。