PostgreSQLのデータをCSVや画面に出力する最適の方法は？コマンドを徹底解説

PostgreSQLのpsqlコマンドでデータをCSV出力したり標準出力に出力する様々な方法を解説します。標準出力にデータを出力する方法、CSV形式でデータを標準出力に出力する方法、CSV形式でファイルに出力する方法、さらにCSV形式かつダブルクオーテーション付きで出力する方法を解説します。

この記事で使用するテーブルとデータ
データを出力(エクスポート)する様々な方法
まとめ

この記事で使用するテーブルとデータ

まずこの記事で使用する「test」テーブルを作成します。id列は自動採番するためserial型にし、name列(名前)とage列(年齢)を用意しています。

CREATE TABLE test (
    id    serial PRIMARY KEY,
    name  text,
    age   int
);

このテーブルに対して、次のような10件のデータを登録します。

INSERT INTO test (name, age)
VALUES
('aaa', 11),
('bbb', 22),
('ccc', 33),
('ddd', 44),
('eee', 55),
('eee', 66),
('eee', 77),
('eee', 88),
('eee', 99),
('eee', 100)
;

以上のコマンドを実行することにより、次のようにtestテーブルに10件のデータを用意出来ました。

=> SELECT * FROM test;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | aaa  |  11 |
|  2 | bbb  |  22 |
|  3 | ccc  |  33 |
|  4 | ddd  |  44 |
|  5 | eee  |  55 |
|  6 | eee  |  66 |
|  7 | eee  |  77 |
|  8 | eee  |  88 |
|  9 | eee  |  99 |
| 10 | eee  | 100 |
+----+------+-----+
(10 rows)

データを出力(エクスポート)する様々な方法

Linuxコマンドライン上でpsqlでデータを標準出力に出力する方法

Linuxコマンドライン上で、psqlコマンドを使ってデータを表示するには次のように「-c」オプションにSQL文を書いて実行する方法です。これで画面上にデータを出力できます。

$ psql -c "SELECT id, name, age FROM test"

 id | name | age
----+------+-----
  1 | aaa  |  11
  2 | bbb  |  22
  3 | ccc  |  33
  4 | ddd  |  44
  5 | eee  |  55
  6 | eee  |  66
  7 | eee  |  77
  8 | eee  |  88
  9 | eee  |  99
 10 | eee  | 100
(10 rows)

CSV形式でデータを出力するには次のように「-A」オプションと「-F,」オプションを付けてpsqlコマンドを実行します。「-A」オプションは「–no-align」という意味で、データを整形しないオプションで、「-F」オプションは指定された区切り文字で列データを区切るという意味です。ここではカンマ区切りにしますので「-F,」としています。

$ psql -A -F, -c "SELECT id, name, age FROM test"

id,name,age
1,aaa,11
2,bbb,22
3,ccc,33
4,ddd,44
5,eee,55
6,eee,66
7,eee,77
8,eee,88
9,eee,99
10,eee,100
(10 rows)

列データ内にカンマ(,)が存在するとEXCELなどでファイルを開いた時にデータが崩れてしまいますので、データ中にカンマが含まれる場合には次のようにして列データをダブルクオーテーションで括ってあげるようにします。\COPYコマンドを使って、データをCSV形式で出力(FORMAT CSV)、ヘッダ行を出力(HEADER TRUE)、データをダブルクオーテーションで括る(FORCE_QUOTE *)、という指定をします。この例での出力先のファイル名は「test.csv」です。

$ psql -c "\COPY (SELECT id, name, age FROM test) TO 'test.csv' (FORMAT CSV, HEADER TRUE, FORCE_QUOTE *)"
COPY 10

$ cat test.csv
id,name,age
"1","aaa","11"
"2","bbb","22"
"3","ccc","33"
"4","ddd","44"
"5","eee","55"
"6","eee","66"
"7","eee","77"
"8","eee","88"
"9","eee","99"
"10","eee","100"

\COPYコマンドでデータを標準出力(コンソール)に出力する

データを標準出力(コンソール)にエクスポートする方法ですが、psqlコマンドの中でCOPYコマンドの出力先として「STDOUT」を指定します。psqlコマンドを起動して、その中で次のコマンドを実行します。

\COPY (SELECT id, name, age FROM test) TO STDOUT;

STDOUTは標準出力を指していますので、このコマンドを実行するとデータが画面上に出力されます。

=> \COPY (SELECT id, name, age FROM test) TO STDOUT;
1       aaa     11
2       bbb     22
3       ccc     33
4       ddd     44
5       eee     55
6       eee     66
7       eee     77
8       eee     88
9       eee     99
10      eee     100
=>

しかしこのままでは、各データが空白区切りになっています。もしCSV形式(カンマ区切り)で出力したい場合には、次のように「WITH CSV」として、CSV形式を指定します。

=> \COPY (SELECT id, name, age FROM test) TO STDOUT WITH CSV;
1,aaa,11
2,bbb,22
3,ccc,33
4,ddd,44
5,eee,55
6,eee,66
7,eee,77
8,eee,88
9,eee,99
10,eee,100

オプションの指定の違いだけですが、「(FORMAT CSV)」というオプションを付ける方法でもCSV形式でデータを出力できます。ヘッダ行も表示させたい場合には、以下のように「HEADER」オプションを付けてあげるとヘッダ行も出力されます。

=> \COPY (SELECT id, name, age FROM test) TO STDOUT (FORMAT CSV, HEADER);
id,name,age
1,aaa,11
2,bbb,22
3,ccc,33
4,ddd,44
5,eee,55
6,eee,66
7,eee,77
8,eee,88
9,eee,99
10,eee,100

なお、もしデータ中にカンマ(,)が入っていると、CSV形式で出力してしまうとEXCELで開いた時にデータが崩れてしまいます。そのような場合には次のように「FORCE_QUOTE *」を指定すると、各列データをダブルクオーテーションで括ってくれます。

=> \COPY (SELECT id, name, age FROM test) TO STDOUT (FORMAT CSV, FORCE_QUOTE *);
"1","aaa","11"
"2","bbb","22"
"3","ccc","33"
"4","ddd","44"
"5","eee","55"
"6","eee","66"
"7","eee","77"
"8","eee","88"
"9","eee","99"
"10","eee","100"

\COPYコマンドでデータをファイルに出力する

データをファイルに出力するには、次のようにCOPYコマンドの結果の出力先をファイル名にします。

\COPY (SELECT id, name, age FROM test) TO 'ファイル名';

次のように「test.txt」というファイルにデータを出力することが出来ます。デフォルトでは結果が空白区切りで出力されます。

=> \COPY (SELECT id, name, age FROM test) TO 'test.txt';
COPY 10

$ cat test.txt
1       aaa     11
2       bbb     22
3       ccc     33
4       ddd     44
5       eee     55
6       eee     66
7       eee     77
8       eee     88
9       eee     99
10      eee     100

CSV形式で出力するには「FORMAT CSV」というオプションを付けてあげるとCSV形式でデータをファイルに出力することが出来ます。

=> \COPY (SELECT id, name, age FROM test) TO 'test.csv' (FORMAT CSV);
COPY 10

$ cat test.txt
1,aaa,11
2,bbb,22
3,ccc,33
4,ddd,44
5,eee,55
6,eee,66
7,eee,77
8,eee,88
9,eee,99
10,eee,100

さらにダブルクオーテーションで括ってデータを出力する場合には「FORCE_QUOTE *」オプションを付けます。

=> \COPY (SELECT id, name, age FROM test) TO 'test.csv' (FORMAT CSV, FORCE_QUOTE *);
COPY 10

$ cat test.txt
"1","aaa","11"
"2","bbb","22"
"3","ccc","33"
"4","ddd","44"
"5","eee","55"
"6","eee","66"
"7","eee","77"
"8","eee","88"
"9","eee","99"
"10","eee","100"

psqlコマンド内でデータを出力する

psqlコマンドの中でデータをファイルに出力するには、「\o」オプションを使います。最初に「-o ファイル名」を指定して出力先を指定し、その後SELECTコマンドを実行します。そしてファイルへの出力を終了するために「\o」を実行します。

=> \o test.txt
=> SELECT id, name, age FROM test;
=> \o

=> \! cat test.txt
 id | name | age
----+------+-----
  1 | aaa  |  11
  2 | bbb  |  22
  3 | ccc  |  33
  4 | ddd  |  44
  5 | eee  |  55
  6 | eee  |  66
  7 | eee  |  77
  8 | eee  |  88
  9 | eee  |  99
 10 | eee  | 100
(10 rows)

この方法でCSVファイルとして出力するには、最初に出力形式と区切り文字を設定して上げる必要があります。

-- 出力形式の指定
\pset format unaligned

-- 区切り文字の指定
\pset fieldsep ','

上記2つの指定をしておいた状態で、「-o」オプションを使ってファイルにデータを出力すると、CSV形式でデータをファイルに出力することが出来ます。

=> \pset format unaligned
Output format is unaligned.

=> \pset fieldsep ','
Field separator is ",".

=> \o test.txt
=> SELECT id, name, age FROM test;
=> \o

$ cat test.txt
id,name,age
1,aaa,11
2,bbb,22
3,ccc,33
4,ddd,44
5,eee,55
6,eee,66
7,eee,77
8,eee,88
9,eee,99
10,eee,100
(10 rows)