読者です 読者をやめる 読者になる 読者になる

psqlでSQLとメタコマンドを併用する

postgre

結論

実行するSQLとメタコマンドが記述されたファイルを用意する
※デリミタがタブ文字の場合は、「ターミナルで Ctrl+v を押してからタブ押下」

delete from zipcode_info;
delete from zipcode_info2;
\copy zipcode_info (pref_code, city_code, zipcode) from data.csv with delimiter ','
\copy zipcode_info2 (pref_code, city_code, zipcode) from data2.csv with delimiter ','

psqlを実行する(上記で用意したファイルをsql.txtとして

psql -U user_name -h host_name -d db_name -f sql.txt -1 2>&1

事の始まり

https://oshiete.goo.ne.jp/qa/8159495.html
やりたいことは、対象テーブルの更新(レコードの削除+流し込み)
上との違いは、対象となるテーブルが複数あることくらい。

調べてみる

copy
http://qiita.com/egg_chicken/items/cec72bda3759f875285f

psql
https://www.postgresql.jp/document/9.1/html/app-psql.html

オプション「-c command」の説明を見ると...

このため、このオプションではSQLpsqlタコマンドを混在させることはできません。 これらを同時に使用するには、echo '\x \\ SELECT * FROM foo;' | psqlのようにパイプを使って文字列をpsqlに渡します(\\はメタコマンドの区切り文字です。)。
コマンド文字列が複数のSQLコマンドを含む場合、トランザクションを複数に分けるBEGIN/COMMITコマンドが明示的に文字列内に含まれない限り、それらのコマンドは1つのトランザクションで処理されます。


SQLとメタコマンドをパイプで渡せば、psqlで実行できそう

やってみる

こういうことはできない

psql -U user_name -h host_name -d db_name -c "delete from zipcode_info; \copy zipcode_info (pref_code, city_code, zipcode) from data.csv with delimiter ','"

から...

echo "select * from zipcode_info;" | psql -U user_name -h host_name -d db_name

的なノリで

echo "delete from zipcode_info; \copy zipcode_info (pref_code, city_code, zipcode) from data.csv with delimiter ','" | psql -U user_name -h host_name -d db_name

できた。
けど、これだとトランザクションが張れていないので…

echo "BEGIN; delete from zipcode_info; \copy zipcode_info (pref_code, city_code, zipcode) from data.csv with delimiter ',' COMMIT;" | psql -U user_name -h host_name -d db_name

\copyのパースに失敗…
いろいろ試してみる

echo "BEGIN; delete from zipcode_info; \copy zipcode_info (pref_code, city_code, zipcode) from data.csv with delimiter ',' \\ COMMIT;" | psql -U user_name -h host_name -d db_name
echo "BEGIN; delete from zipcode_info; \copy zipcode_info (pref_code, city_code, zipcode) from data.csv with delimiter ',' \n COMMIT;" | psql -U user_name -h host_name -d db_name

タメだ…区切り文字が分からない…

もう一度調べてみる

psql
https://www.postgresql.jp/document/9.1/html/app-psql.html

オプション「-f filename」の説明を見ると...

対話式にコマンドを読み取るのではなく、filenameファイルをコマンドのソースとして使用します。 このファイルの処理が終了した後、psqlは終了します。 これは\i内部コマンドとほぼ同じ効力を持ちます。


オプション「-1」の説明を見ると...

fオプションを使用してpsqlスクリプトを実行する時、このオプションを併記すると、スクリプトをBEGIN/COMMITで囲み、単一トランザクション内でスクリプトを実行します。 これにより確実にすべてのコマンドが完全に成功するか、変更がまったく行われないかのいずれかになります。


これだ

もう一度やってみる

実行するSQLとメタコマンドが記述されたファイルを用意する

delete from zipcode_info;
\copy zipcode_info (pref_code, city_code, zipcode) from data.csv with delimiter ','

トランザクション張る必要があるからオプション付けて

psql -U user_name -h host_name -d db_name -f sql.txt -1

できた(エラーが発生すると反映されない、トランザクションが効いてる

対象となるテーブルが複数ある場合は、複数のSQLとメタコマンドが記述されたファイルを用意して

delete from zipcode_info;
delete from zipcode_info2;
\copy zipcode_info (pref_code, city_code, zipcode) from data.csv with delimiter ','
\copy zipcode_info2 (pref_code, city_code, zipcode) from data2.csv with delimiter ','

psqlコマンドを実行してみる

psql -U user_name -h host_name -d db_name -f sql.txt -1 2>&1

できてる

仮にできなかったら

psqlコマンドで個々のSQLやメタコマンドは実行できるから、トランザクション機能をどこかで補完する必要がある。
オブサーバ的なテーブルで参照先のテーブルを切り替えることで、トランザクション機能を肩代わりすれば問題ない。
本来想定していたテーブルと合わせてミラーリング用のテーブルを用意し、更新処理が正常に終わったら参照するテーブルを切り替える。