【PostgreSQL】psqlでクライアント側からCSVを入出力する

\copyでテーブルをファイルにエクスポートする

psqlの\copyメタコマンドは、SQLのCOPYコマンドとほぼ同じ構文でテーブルとファイルの間でデータをやり取りする。テーブル全体をCSVファイルへ出力するにはTOにファイルパスを指定する。

testdb=# \copy users TO 'users.csv' WITH CSV HEADER
COPY 2

$ cat users.csv
id,name,email
1,alice,alice@example.com
2,bob,bob@example.com

WITH CSV HEADERでCSV形式かつヘッダー行を出力する。オプションはSQLのCOPYと同じものが使える。

クエリの結果をエクスポートする

テーブル名の代わりに括弧で囲んだクエリを指定すると、そのクエリの結果をエクスポートできる。

testdb=# \copy (SELECT name, email FROM users WHERE id = 1) TO 'one.csv' WITH CSV
COPY 1

$ cat one.csv
alice,alice@example.com

集計結果や条件を絞り込んだ結果だけをエクスポートしたい場合に使う。

CSVファイルをテーブルにインポートする

FROMにファイルパスを指定すると、CSVファイルの内容をテーブルへ取り込む。

testdb=# \copy users_import(name, email) FROM 'one.csv' WITH CSV
COPY 1
testdb=# SELECT * FROM users_import;
 id | name  |       email
----+-------+-------------------
  3 | alice | alice@example.com
(1 row)

列リスト(name, email)を指定すると、CSVに含まれないid列は自動採番に任せられる。

\copyとCOPYの違い

\copyはSQLのCOPYコマンドをpsqlがラップしたメタコマンドである。構文はほぼ同じだが、実行される場所とアクセスできるファイルが異なる。

	\copy(psqlメタコマンド)	COPY(SQLコマンド)
実行場所	psqlを実行しているクライアント側	PostgreSQLサーバー側
アクセスできるファイル	psqlの実行ユーザーが読み書きできるクライアントのファイル	サーバープロセスの実行ユーザーが読み書きできるサーバーのファイル
必要な権限	テーブルに対するSELECT/INSERT権限のみ	テーブル権限に加えてスーパーユーザーまたは`pg_read_server_files`/`pg_write_server_files`ロールへの所属(PostgreSQL 11以降)

リモートのサーバーに接続している場合、COPYに指定するファイルパスはサーバー側のパスとして解釈される。手元のPCにあるCSVファイルをリモートのPostgreSQLへ取り込みたい場合は\copyを使う。\copyはファイルの内容をいったんクライアント・サーバー間の通信で転送してから処理するため、サーバーのファイルシステムに一切アクセスしない。

一般ユーザーの接続でCOPY ... TO 'file'を実行すると権限エラーになる。

testdb=# COPY users TO '/tmp/users.csv' WITH CSV HEADER;
ERROR:  must be superuser or have privileges of the pg_write_server_files role to COPY to a file

参考: PostgreSQL Documentation: COPY

標準出力・標準入力を使う

ファイルパスの代わりにパイプ|を使うと、シェルコマンドの標準出力・標準入力と直接やり取りできる。

testdb=# \copy users TO PROGRAM 'gzip > users.csv.gz' WITH CSV HEADER
COPY 2

シェルのリダイレクトと組み合わせて、psqlの標準出力そのものへ書き出すことも可能である。

$ psql -d testdb -c "\copy users TO STDOUT WITH CSV HEADER" > users.csv

grepやjqなど他のコマンドへパイプでつなげる場合に使う。

TSV形式で入出力する

FORMAT csvの代わりにDELIMITERを指定すると、CSV以外の区切り文字でも入出力できる。

testdb=# \copy users TO 'users.tsv' WITH (FORMAT csv, DELIMITER E'\t', HEADER)
COPY 2

$ cat users.tsv
id	name	email
1	alice	alice@example.com
2	bob	bob@example.com

コマンドラインの-A -Fオプションでも同様の区切り文字を指定できるが、あちらはクエリ結果の表示形式そのものを変更するオプションであり、\copyとは仕組みが異なる。テーブル全体ではなくクエリ結果だけをTSVで欲しい場合は、-A -Fオプションの利用も検討する。

参考: 【PostgreSQL】psqlコマンドでcsv, tsv形式のクエリ結果を出力する

具体例: 別のデータベースへテーブルをコピーする

\copyはファイル経由になるが、シェルのパイプを使うと2つのpsqlコマンドの間でファイルを介さずデータを受け渡せる。

$ psql -d source_db -c "\copy users TO STDOUT WITH CSV" | psql -d dest_db -c "\copy users FROM STDIN WITH CSV"

異なるサーバー間でテーブルの中身だけを移行したい場合、pg_dump/pg_restoreほど大掛かりな準備をせずに済む。大量データの移行には向かないが、少量のマスタデータのコピーには手軽である。

参考

psql - PostgreSQL Documentation