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

結論

実行するSQLとメタコマンドが記述されたファイルsqlite.txtを用意する。
※デリミタがタブ文字の場合は、カンマを「Ctrl+v+tab」に置き換える。

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コマンドでトランザクション - その他（プログラミング・Web制作）締切済 | 教えて!goo

テーブルの更新（レコードの削除＋レコードの流し込み）を行いたい。
上との違いは、対象となるテーブルが複数あることくらい。

調べてみる

copy
CSVファイルをデータベースにインポートする - Qiita

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

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

このため、このオプションではSQLとpsqlメタコマンドを混在させることはできません。これらを同時に使用するには、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やメタコマンドは実行できるから、トランザクション機能をどこかで補完する必要がある。
オブサーバ的なテーブルで参照先のテーブルを切り替えることで、トランザクション機能を肩代わりすれば問題ない。
本来想定していたテーブルと合わせてミラーリング用のテーブルを用意し、更新処理が正常に終わったら参照するテーブルを切り替えることで対応する。

技術メモ