Postgresのデータをファイルへ書き出す (psqlでCopyコマンド)

概要

リモートのPostgreSQLサーバからローカルにCSVファイルとしてテーブルをExportする。

COPYと\copyコマンド

COPY: テーブルとpostgresqlユーザ操作可能なファイル間

COPY (SELECT * FROM country WHERE country_name LIKE 'A%') TO '/usr1/proj/bray/sql/a_list_countries.copy';

The file must be accessible by the PostgreSQL user (the user ID the server runs as) and the name must be specified from the viewpoint of the server.

リモートにPostgreSQLがある場合には、PostgreSQL userがローカルのファイルにアクセスできないので、COPYコマンドは使えない。

\copy: データベースサーバとファイルのサーバが違くても大丈夫 (リモート可)

Do not confuse COPY with the psql instruction \copy. \copy invokes COPY FROM STDIN or COPY TO STDOUT, and then fetches/stores the data in a file accessible to the psql client. Thus, file accessibility and access rights depend on the client rather than the server when \copy is used.

\copy は、COPY FROM STDINやCOPY TO STDOUTを実行し、それからそのデータをpsqlクライアントがアクセス可能なファイルから読み取り/に書き込む。

使い方

リモートインスタンスとローカルファイルの操作に関しては、 psql -c を使う:

psql <database> \
    -U <db_user> \
    -p <db_port> \
    -h <db_host> \
    -c "\copy <table> from '/path/to/table.csv' with DELIMITER ','"

具体例

例1. \COPY FROM: ファイルからテーブルへ書き込む場合

\copy <table> from '/path/to/table.csv' with DELIMITER ','

例2. \COPY TO: テーブルからファイルへ書き込む場合

\copy <table> to '/path/to/table.csv' with DELIMITER ','

例3. \COPY TO: SQLを使って必要なデータのみテーブルからファイルへコピーする場合

CopyしたいものをSQLで書くこともできる。

\copy (select field_a, field_b from table_a where field_c = 10;) to '/path/to/table.csv' with DELIMITER ','

例4. \COPY TO: CSVでHeaderをつける

\copy <table> TO '/path/to/table.csv' with delimiter ',' csv header

例5. テーブルをSTDOUTに出す

\copy <table> TO STDOUT with delimiter ','

例6. STDINからインポート

\copy <table> FROM STDIN with delimiter ','

References

• 【PostgreSQL】一括でデータを入出力できるCOPYコマンド
• COPY — copy data between a file and a table
• psql — PostgreSQL interactive terminal
• \copy コマンドを使用して PostgreSQL DB インスタンスのテーブルにデータをインポートする
  
• Postgres: \copy syntax