PostgreSQLで、テーブルをCSVファイルに書き出す（エクスポートする）方法についてです。
- 動作確認環境：
- PostgreSQL 9.6.4
- Windows10
大きく分けて2つある。といっても両方psqlコマンドを使うのだが。。。
https://www.postgresql.jp/document/9.6/html/app-psql.html
psqlを直接使う
psql データベース名 -c "SQL文" -A -F, > 出力するファイル名
こんな感じ。
-Aは位置揃えなしの出力モードに切り替え、
-Fはフィールド区切り文字の指定。例では「,(カンマ)」を指定してる。
これ手軽だけどいくつか弱点がある。
- 項目の ”（ダブルクォーテーション）括りができない
- タブ区切りにするのがめんどい（コマンドプロンプト自体の設定変更が必要）
- NULL文字が変更できない
- 列名を付けると、もれなく"結果の行数フッタ"がついてくる。フッタ行だけ外すのできない。
COPYコマンドを使う
psqlコマンドでCOPYコマンドを発行する。これならダブルクォーテーション括りも楽々ですよ！
コマンドはバッチファイル化する。
postgres_export.bat
@echo off
cd /d %~dp0
set PGHOST=192.168.0.10
set PGDATABASE=dbname
set PGUSER=postgresuser
set PGPASSWORD=postgrespassword
set PSQL_COMMAND="C:\Program Files\PostgreSQL\9.6\bin\psql.exe"
%PSQL_COMMAND% -c "COPY (SELECT * FROM hogehoge LIMIT 100) TO STDOUT WITH CSV DELIMITER E'\t' FORCE QUOTE * NULL AS '' HEADER;" > C:\export.csv
これで タブ区切り、引用符囲み、NULLは空文字、カラム名表示 になる。
https://www.postgresql.jp/document/9.6/html/sql-copy.html
STDOUTで標準出力に出したのを、先のpsqlと同じようにファイルに流しているだけですね。
バッチにしてるのは、PostgreSQLのpsqlコマンドは引数でパスワードを設定できないから。手入力じゃ自動化しんどいので。
パス等は適当に書き換えてください。コマンドプロンプトでもPowershellでも動きます。
クエリが長かったり複数行に分かれるときは、-cオプションやめて -fオプションでファイルにしてもいいかも。
query.sql
COPY (
SELECT * FROM hogehoge
LEFT JOIN fugafuga ON hogehoge.piyo = fugafuga.piyo
LIMIT 100
) TO STDOUT WITH CSV DELIMITER E'\t' FORCE QUOTE * NULL AS '' HEADER;
postgres_export.bat
@echo off
cd /d %~dp0
set PGHOST=192.168.0.10
set PGDATABASE=dbname
set PGUSER=postgresuser
set PGPASSWORD=postgrespassword
set PSQL_COMMAND="C:\Program Files\PostgreSQL\9.6\bin\psql.exe"
%PSQL_COMMAND% -f query.sql > C:\export.csv