PostgreSQLでファイル出力

time 2018/02/02

folder PostgreSQL

本記事では、PostgreSQLにおいてDBのデータからファイルを出力する方法について、まとめました。

ファイルの出力は、他システムとの連携等でよく使われます。

メタコマンド¥copy

psql内で入力されたコマンドのうち、バックスラッシュ（日本語入力環境だと一般的には円マーク）で始まるコマンドをpsqlのメタコマンドと呼びます。

そのうちの一つである¥copyを使うことで、ファイルへの出力を行うことができます。

使用方法

文法
¥copy テーブル名など to ‘パス＆ファイル名’ with csv

説明
テーブルをcsv形式でファイルに出力します。クライアントの環境にファイル出力されます。
with csvとオプションをつけることで、csv形式にできます。省略するとテキスト形式になりますが、csv形式の方がオプション色々使えるので便利かと思います。
パスは、絶対パスか、psqlを起動した際のパスからの相対パスで指定します。

-- テーブルデータ
SELECT * FROM test1;
 c1 | c2 |  c3   |     c4     |  c5
----+----+-------+------------+------
  1 | 1  | 00001 | 2018-01-01 | 7628
  2 | 1  | 00002 |            | 2639
  3 | 1  | 00003 | 2018-01-03 |
(3 rows)

-- ファイル出力
¥copy test1 to '/xxx/test1.log' with csv

-- 出力ファイル内容
1,1,00001,2018-01-01,7628
2,1,00002,,2639
3,1,00003,2018-01-03,

-- テーブルデータ

SELECT * FROM test1;

c1 | c2 | c3 | c4 | c5

----+----+-------+------------+------

1 | 1 | 00001 | 2018-01-01 | 7628

2 | 1 | 00002 | | 2639

3 | 1 | 00003 | 2018-01-03 |

(3 rows)

-- ファイル出力

¥copy test1 to '/xxx/test1.log' with csv

-- 出力ファイル内容

1,1,00001,2018-01-01,7628

2,1,00002,,2639

3,1,00003,2018-01-03,

出力項目を指定する
テーブル全体を出力するだけでなく、項目を指定して出力することも可能です。
項目を指定する場合は、テーブル名の個所をテーブル名(項目名,項目名,…)と記述します。

-- ファイル出力（出力項目を指定）
¥copy test1(c1,c4,c5) to '/xxx/test1.log' with csv

-- 出力ファイル内容
1,2018-01-01,7628
2,,2639
3,2018-01-03,

-- ファイル出力（出力項目を指定）

¥copy test1(c1,c4,c5) to '/xxx/test1.log' with csv

-- 出力ファイル内容

1,2018-01-01,7628

2,,2639

3,2018-01-03,

クエリ結果を出力する
クエリ結果を出力することも可能です。
項目を指定する場合は、テーブル名の個所を(SELECT～)のようにクエリを記述します。注意点としては、カッコでくくる必要があります。

-- ファイル出力（クエリ）
¥copy (SELECT c1,c4,c5 FROM test1 WHERE c1=1) to '/xxx/test1.log' with csv

-- 出力ファイル内容
1,2018-01-01,7628

-- ファイル出力（クエリ）

¥copy (SELECT c1,c4,c5 FROM test1 WHERE c1=1) to '/xxx/test1.log' with csv

-- 出力ファイル内容

1,2018-01-01,7628

タブ区切りで出力する
区切り文字を変更することも可能です。
区切り文字を変更には、delimiter ‘x’（任意の1バイト文字）のオプションをつけます。
タブ区切りにするには、delimiter E’¥t’とします。

-- ファイル出力（タブ区切り）
¥copy test1 to '/xxx/test1.log' with csv delimiter E'¥t'

1 2	-- ファイル出力（タブ区切り） ¥copy test1 to '/xxx/test1.log' with csv delimiter E'¥t'

項目名のヘッダーつきで出力する
項目名をヘッダーとして出力することも可能です。
項目名をヘッダーとして出力には、headerのオプションをつけます。

-- ファイル出力（ヘッダーつき）
¥copy test1 to '/xxx/test1.log' with csv header

-- 出力ファイル内容
c1,c2,c3,c4,c5
1,1,00001,2018-01-01,7628
2,1,00002,,2639
3,1,00003,2018-01-03,

-- ファイル出力（ヘッダーつき）

¥copy test1 to '/xxx/test1.log' with csv header

-- 出力ファイル内容

c1,c2,c3,c4,c5

1,1,00001,2018-01-01,7628

2,1,00002,,2639

3,1,00003,2018-01-03,

注意点
ビューに対して使用することはできません。ただし、応用２のようにクエリを使用すれば可能です。

-- ファイル出力（ビュー指定）
¥copy test1_v to '/xxx/test1_v.log' with csv
ERROR:  cannot copy from view "test1_v"
HINT:  Try the COPY (SELECT ...) TO variant.

-- ファイル出力（ビューをクエリを使用し出力）
¥copy (SELECT * FROM test1_v) to '/xxx/test1_v.log' with csv

-- 出力ファイル内容
1,1,00001,2018-01-01,7628
2,1,00002,,2639
3,1,00003,2018-01-03,

-- ファイル出力（ビュー指定）

¥copy test1_v to '/xxx/test1_v.log' with csv

ERROR: cannot copy from view "test1_v"

HINT: Try the COPY (SELECT ...) TO variant.

-- ファイル出力（ビューをクエリを使用し出力）

¥copy (SELECT * FROM test1_v) to '/xxx/test1_v.log' with csv

-- 出力ファイル内容

1,1,00001,2018-01-01,7628

2,1,00002,,2639

3,1,00003,2018-01-03,

COPYコマンド

psqlでCOPYコマンドを使用することでも、ファイルとの出力を行うことができます。

使用方法

文法
COPY テーブル名など TO ‘パス＆ファイル名’ WITH CSV;

説明
基本的には、¥copyと同じです。オプションも含めて同じように使うことができます。ただし、大きな違いが2点あります。

1点目は、データベースのスーパーユーザのみファイル出力が可能という点です。
2点目は、サーバーの環境にファイル出力されるという点です。クライアントから接続している場合でも、サーバー環境にファイル出力されます。（パスもサーバーのパスを記述する、絶対パス推奨）

上記の制限から、メタコマンドの¥copyの方が使いやすいと思います。しかし、COPYコマンドの方が早いという情報もあります。（近いうちに検証予定）

-- ファイル出力
COPY test1 TO '/xxx/test1.log' WITH CSV

-- 出力ファイル内容
1,1,00001,2018-01-01,7628
2,1,00002,,2639
3,1,00003,2018-01-03,

-- ファイル出力（一般ユーザーで実行）
COPY test1 TO '/xxx/test1.log' WITH CSV
ERROR:  must be superuser to COPY to or from a file
HINT:  Anyone can COPY to stdout or from stdin. psql's ¥copy command also works for anyone.