Choco Soft

仕事でお客様からデータをEXCELとかで預かって、データベースに投入する時に割と苦労しませんか？

受領したEXCELファイルのフォーマットはバラバラ、データ内容に不具合ありーの。。。。
そんな時は、正しいデータだけを先に入れて後でその差分をゆっくり直したいですよね。

今回はデータベースにPostgreSQLを使っている人向けのTIPSです。
PostgreSQLのバージョン【９．５】以上は、「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なんかで実行するよりも数百倍速いです。