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

覚えたら書く

IT関係のデベロッパとして日々覚えたことを書き残したいです。 twitter: @yyoshikaw

PostgreSQL - DBのデータをCSVに出力したい

DBのデータをCSV出力したいという場面は多々あると思います。

PostgreSQLにpsqlで接続して特定のコマンドを実行すればCSVファイルへの出力ができます。


テーブルの全データをCSVに出力する

単純に特定のテーブルの全データをCSV出力する場合は以下のコマンドを実行すれば可能です

\COPY {table名} TO '{出力先csvファイルのフルパス}' WITH CSV DELIMITER ',';

■実行例

\COPY sampletbl1 TO '/tmp/sampletbl1.csv' WITH CSV DELIMITER ',';

これを実行すると対象としたテーブルの全データが指定のcsvファイルに出力されます


CSVに出力するカラムを絞る

最初の例に従うと対象テーブルの全カラムが出力対象となります。
出力するカラムを指定したい場合は以下のようにします

\COPY {table名}({column名1}, {column名2}, {column名3}) TO '{出力先csvファイルのフルパス}' WITH CSV DELIMITER ','

■実行例

\COPY users(id, name, age) TO '/tmp/users.csv' WITH CSV DELIMITER ','

これを実行すると対象としたテーブルの指定のカラムのデータだけがcsvファイルに出力されます


カラム名もCSVに出力したい

これまでの例は、カラム名はCSVファイルに出力されません。
CSVの先頭行(ヘッダ)にカラム名を出力する場合は、HEADER という指定をします

\COPY {table名}({column名1}, {column名2}, {column名3}) TO '{出力先csvファイルのフルパス}' WITH CSV DELIMITER ',' HEADER

■実行例

\COPY users(id, name, age) TO '/tmp/users.csv' WITH CSV DELIMITER ',' HEADER

これを実行するとデータだけではなくcsvファイルのヘッダにカラム名も出力されます


SQLの実行結果をCSV出力する

CSVに出力したいデータの絞り込みや並び替えなど各種条件を指定したい場合は、SQLを実行した結果をCSVに出力するのが早いです。
以下のようにして実行します

\COPY ({SQL文(SELECT文)}) TO '{出力先csvファイルのフルパス}' WITH CSV DELIMITER ',' HEADER

■実行例

\COPY (SELECT id, name, age, where type = 0 order by registerdate limit 100) TO '/tmp/users.csv' WITH CSV DELIMITER ',' HEADER

これを実行するとSQLで得られた結果がcsvファイルに出力されます


値をダブルクォートで囲みたい

CSVに出力するデータをダブルクォート(")で囲みたい場合は FORCE QUOTE * という指定をします

\COPY ({SQL文(SELECT文)}) TO '{出力先csvファイルのフルパス}' WITH CSV DELIMITER ',' HEADER FORCE QUOTE *

■実行例

\COPY (SELECT id, name, age, where type = 0) TO '/tmp/users.csv' WITH CSV DELIMITER ',' HEADER FORCE QUOTE *