PostgreSQL の COPY コマンドを使いこなす
PostgreSQL はデータの入出力に COPY コマンドという強力な機能を備えています。このコマンドを使うことでデータベースとファイルとのやり取りを効率化出来ます。INSERT 文を羅列してデータベースにデータを登録したり、psql コマンドの "\o" を使ってデータベースのデータをファイルに出力することも出来ますが、COPY コマンドの方が直感的にわかりやすくパフォーマンスも良いです。
COPY コマンドと \COPY コマンドの違いを知る
COPY コマンドには "COPY" コマンドと "\COPY" コマンドの２種類があります。まずこの違いを把握しておくことで、ここからの説明を正しく理解できるようになります。COPY コマンドは、PostgreSQL データベースが稼働しているサーバー上のファイルの入出力を行うためのものです。つまり PostgreSQL サーバーに psql や PgAdmin3 等でデータベース接続したとしても、入出力を行うファイルは必ずデータベースサーバー上になければならないということになります。
他方の "\COPY" はデータベース接続を行ったクライアント側でファイルの入出力を行うものです。psql コマンドでリモートの PostgreSQL データベースサーバーに接続した場合であれば、接続元のファイルの入出力を行うことが出来ます。
テストデータの準備
ここでは下記のような id 列と name 列を持つ colors という CSV ファイルと、テーブルを用意します。またこのファイルにはヘッダ行が含まれています。このデータを使って CSV ファイルとテーブル間の入出力を行います。$ cat colors.csv
id,name
1,red
2,blue
3,yellow
4,pink
5,green
=> \d colors
Table "public.colors"
+--------+---------+-----------+
| Column | Type | Modifiers |
+--------+---------+-----------+
| id | integer | |
| name | text | |
+--------+---------+-----------+
CSV ファイルをテーブルに読み込む
では colors.csv ファイルの内容を、同じく colors テーブルに取り込んでみます。psql コマンドを使ってデータを取り込みますので \COPY コマンドを使います。また COPY 及び \COPY コマンドは、次のような書式を使います。COPY テーブル名 FROM ファイル名 (ファイルからテーブルにデータを読み込む) COPY テーブル名 TO ファイル名 (テーブルからファイルにデータを出力) \COPY テーブル名 FROM ファイル名 (ファイルからテーブルにデータを読み込む) \COPY テーブル名 TO ファイル名 (テーブルからファイルにデータを出力)
それでは早速 CSV データをテーブルに読み込んでみます。ファイルはカンマ区切りの CSV ファイルとなっていますので、読み込む対象は CSV である、と \COPY コマンドに伝えます。また読み込むファイルにはヘッダ行があるので、それを読み込まないために header という命令を付けています。ファイルからテーブルにデータを読み込むので、"\COPY テーブル名 FROM ファイル名" という書式を使います。
=> \COPY colors FROM 'colors.csv' CSV HEADER => SELECT * FROM colors; +----+--------+ | id | name | +----+--------+ | 1 | red | | 2 | blue | | 3 | yellow | | 4 | pink | | 5 | green | +----+--------+ (5 rows)
テーブルのデータをファイルに出力する
今度は逆にテーブルのデータをファイルに出力してみます。テーブルからファイルにデータを出力するので、"\COPY テーブル名 TO ファイル名" という書式を使います。またファイルはカンマ区切りの CSV 形式で、ヘッダ行もファイルに出力しますので、CSV と HEADER というオプションを付け加えています。=> \COPY colors TO 'test.csv' CSV HEADER $ cat test.csv id,name 1,red 2,blue 3,yellow 4,pink 5,green
タブ区切りのファイルを入出力する
今度はファイルの区切り文字がタブ文字(\t)である場合の \COPY コマンドでのファイルの入出力を行ってみます。先程のカンマ区切りの CSV ファイルとの違いは "DELIMITER E'\t'" というオプションを付けてタブ文字が区切り文字であることを明示します。E'\t' というのは、PostgreSQL では区切り文字が "\" と "t" を別々に解釈して２文字の区切り文字と認識してしまいます。しかし区切り文字を示す DELIMITER は１文字(１バイト)しか受け付けないため、E という文字を付けて \ を特殊文字として扱うようにしています。これにより正しくタブ文字を認識できるようになっています。
まずタブ区切りのファイルをテーブルに読み込んでみます。
$ cat colors.tsv id name 1 red 2 blue 3 yellow 4 pink 5 green => DELETE FROM colors; => \COPY colors FROM 'colors.tsv' CSV DELIMITER E'\t' HEADER => SELECT * FROM colors; +----+--------+ | id | name | +----+--------+ | 1 | red | | 2 | blue | | 3 | yellow | | 4 | pink | | 5 | green | +----+--------+ (5 rows)
次にテーブルのデータをタブ区切りのファイルに出力してみます。
=> \COPY colors TO 'test.tsv' CSV DELIMITER E'\t' HEADER $ cat test.tsv id name 1 red 2 blue 3 yellow 4 pink 5 green
各項目をダブルクォーテーションで囲む
データ中にカンマやダブ文字が入っているファイルがある場合、各列の区切りがどこからどこまでなのかわからなくなります。その場合には各列をダブルクォーテーションで囲んであげると EXCEL でファイルを開いた場合でも問題なくデータを解釈できます。ここでは検証のためにあえて文字列の中にカンマ(,)を付けており、また各列の値は全てダブルクォーテーションで囲んでいます。
$ cat colors2.csv id,name "1","red" "2","bl,ue" "3","yel,low" "4","pi,nk" "5","gr,een" => DELETE FROM colors; => \COPY colors FROM 'colors2.csv' CSV HEADER QUOTE '"' => SELECT * FROM colors; +----+---------+ | id | name | +----+---------+ | 1 | red | | 2 | bl,ue | | 3 | yel,low | | 4 | pi,nk | | 5 | gr,een | +----+---------+ (5 rows) => \COPY colors TO 'foobar.csv' CSV HEADER QUOTE '"' $ cat foobar.csv id,name 1,red 2,"bl,ue" 3,"yel,low" 4,"pi,nk" 5,"gr,een"
単に QUOTE オプションを付けただけの場合、上記のように一部文字列だけがダブルクォーテーションで囲まれています。しかしこれでは都合が悪い場合、次のようにして FORCE オプションにアスタリスク (*) を付けることで全ての列の値を囲むことが出来ます。
=> \COPY colors TO 'foobar.csv' CSV FORCE QUOTE * $ cat foobar.csv id,name "1","red" "2","bl,ue" "3","yel,low" "4","pi,nk" "5","gr,een"
ある列だけをダブルクォーテーションで囲みたい場合には、次のように FORCE QUOTE オプションに続けて列名を指定します。複数列を指定する場合には各列名をカンマ区切りで指定します。
=> \COPY colors TO 'foobar.csv' CSV HEADER FORCE QUOTE name $ cat foobar.csv id,name 1,"red" 2,"bl,ue" 3,"yel,low" 4,"pi,nk" 5,"gr,een" => \COPY colors TO 'foobar.csv' CSV HEADER FORCE QUOTE id,name $ cat foobar.csv id,name "1","red" "2","bl,ue" "3","yel,low" "4","pi,nk" "5","gr,een"
またもしデータの文字列中にダブルクォーテーションがある場合には、例えばシングルクォーテーション(')で各列の値を囲ってあげると良いでしょう。
=> \COPY colors TO 'xyz.csv' CSV HEADER QUOTE '''' $ cat xyz.csv id,name 1,red 2,'bl,ue' 3,'yel,low' 4,'pi,nk' 5,'gr,een'
テーブルのデータを標準出力に表示する
\COPY コマンドはファイルへの入出力だけでなく、標準出力にもデータを出力することが出来ます。標準出力は stdout として利用することができ、出力ファイル名を stdout にすればデータをファイルではなく画面に出力することが出来ます。=> \COPY colors TO stdout 1 red 2 bl,ue 3 yel,low 4 pi,nk 5 gr,een => \COPY colors TO stdout CSV HEADER FORCE QUOTE * id,name "1","red" "2","bl,ue" "3","yel,low" "4","pi,nk" "5","gr,een"
標準入力から読み込んだデータをテーブルに登録する
今度は標準入力からデータを読み込んでテーブルにデータを登録してみます。標準入力は stdin を使うことで利用できるもので、入力ファイル名の代わりに stdin を指定します。stdin を指定して \COPY コマンドを実行すると、次のようにデータを１行づつ改行しながら入力するよう指示されます。ヘッダ行も含めて１行づつ入力してみます。またデータの終わりを示すためにバックスラッシュとドット "\." を最後の行に入力すると、標準入力からのデータが終了したことが伝わります。
=> \COPY colors FROM stdin CSV HEADER Enter data to be copied followed by a newline. End with a backslash and a period on a line by itself. >> id,name >> 1,red >> 2,blue >> 3,yellow >> 4,pink >> 5,green >> \. => SELECT * FROM colors; +----+--------+ | id | name | +----+--------+ | 1 | red | | 2 | blue | | 3 | yellow | | 4 | pink | | 5 | green | +----+--------+ (5 rows)
任意の SQL を \COPY コマンドで利用する
ここまではテーブル内の全データをファイルや標準入出力(画面)との間でやり取りしてきましたが、今度は任意の SQL を実行した結果をファイルや標準出力に出力することが出来ます。書式は "\COPY (任意のSQL) TO [ファイル名又は stdout]" となります。SQL を括弧 "()" で囲んで書いてあげるだけです。
以下では標準出力(画面)に SQL を実行した結果をカンマ区切りで標準出力(画面)に出力してみます。
=> \COPY (SELECT id, name FROM colors WHERE name IN ('red','blue')) TO stdout CSV HEADER
id,name
1,red
2,blue
なお私が試した限りでは、SQL は改行できませんので全て１行で書く必要があるようです。
\COPY コマンド内で Linux コマンドを使う
\COPY コマンドには PROGRAM というオプションがあり、これを使うと \COPY コマンド実行とともに Linux コマンドを実行することが出来ます。以下の例では colors テーブルを Linux の圧縮コマンドである gzip コマンドに結果を渡して /tmp/colors.gz という圧縮ファイルを作成します。もちろんファイルの中にはテーブルのデータが CSV 形式で正しく出力されています。
=> \COPY colors TO PROGRAM 'gzip > /tmp/colors.gz' CSV HEADER $ zcat /tmp/colors.gz id,name 1,red 2,blue 3,yellow 4,pink 5,green
PL/PgSQL 関数の中で COPY コマンドを使う
今度は PostgreSQL の関数(ファンクション)である PL/PgSQL を使って、関数内で COPY コマンドを使ってみます。関数は PostgreSQL サーバー内で動作するので \COPY コマンドではなく COPY コマンドを使う必要があります。冒頭でも述べましたが \COPY コマンドは psql を経由して利用するものであるのに対して、COPY コマンドは PostgreSQL データベースが稼働しているサーバー上のファイルの入出力を行うためのものです。PL/PgSQL 関数はサーバー内で動作するので COPY コマンドを使う必要がある、ということになります。
まず以下のような PL/PgSQL 関数を書いてみました。この関数は単純に COPY コマンドを使って colors テーブルの内容を全て /tmp/colors.csv というファイルに出力します。関数が成功したら true を返しています。
CREATE OR REPLACE FUNCTION generate_csv() RETURNS boolean AS $BODY$ DECLARE var_sql text; BEGIN RAISE NOTICE 'Start generating CSV file.'; var_sql := 'COPY colors TO ''/tmp/colors.csv'' CSV HEADER ;'; RAISE NOTICE '%', var_sql; EXECUTE var_sql; RAISE NOTICE 'Generated CSV file.'; RETURN true; END; $BODY$ LANGUAGE plpgsql VOLATILE SECURITY DEFINER;
この関数は postgres スーパーユーザーで作成する必要があります。スーパーユーザーでないとファイルシステムへの書き込みが出来ないので、必ず関数の所有者が postgres ユーザーになっていることを確認して下さい。PL/PgSQL 関数内で上手く CSV ファイルを出力できているのが分かります。
$ psql -U postgres =# SELECT * FROM generate_csv(); NOTICE: Start generating CSV file. NOTICE: COPY colors TO '/tmp/colors.csv' CSV HEADER ; NOTICE: Generated CSV file. +--------------+ | generate_csv | +--------------+ | t | +--------------+ (1 row) =# \q $ cat /tmp/colors.csv id,name 1,red 2,blue 3,yellow 4,pink 5,green
