【PostgreSQL】一括でデータを入出力できるCOPYコマンド

こんにちは。新米🍚エンジニアのしげぞうです。

前回は、AWSの社内勉強会に参加したときのことについて書いたのですが、
今回は先日携わった業務で使用したPostgreSQLのCOPYコマンドについて書いてみたいと思います。

COPYコマンドとは

PostgreSQLのCOPYコマンドは、テーブルとDBサーバ内のファイル間でデータのやり取り（読み込み/書き出し）をするためのコマンドです。

負荷テスト用に大量データを投入する際に利用したり、テキストベースでバックアップを取ったりする際に利用することができます。

ちなみに、pg_dumpコマンドでダンプをスクリプト形式で出力した際は、データ投入部分がCOPYコマンドで構成されていたりします。

それぞれ、以下のコマンドで実行することができます。

-- データの読み込み。ファイルからテーブルにデータをコピーする。
COPY table_name (column_name [, ...]) FROM '/xxxxx/filename';

-- データの書き出し。テーブルの内容をファイルにコピーする。
COPY table_name TO '/xxxxx/filename';

FROM,TO句には、DBサーバ内の絶対パスで対象ファイルを指定します。

ファイルの種類としては、テキスト, CSV, バイナリ形式が利用できますが、CSVでのやり取りが良いかと思います。

また、対象データベースのスーパーユーザで実行する必要があります。

COPY FROM

では実際に、データの取り込みをやってみます。

例えば、こんなテーブルと取り込み用データを準備したとすると、

postgres=# SELECT * FROM test_table;
 id | food_name |
----+------------
(0 rows)

=======================================================
=======================================================

$ cat /xxxxx/test_table.csv
id,food_name
1,りんご
2,パン
3,ハンバーグ

下記のコマンドでデータを取り込むことができます。

-- ファイルからデータを取り込む。
-- csvから取り込むので、CSV パラメータを指定。
-- ヘッダーを読み込まないようにするため、HEADER オプションを指定。
COPY test_table (id, food_name) FROM '/xxxxx/test_table.csv' WITH CSV HEADER;

実際にコマンドを流してみると、、

postgres=# COPY test_table (id, food_name) FROM '/xxxxx/test_table.csv' WITH CSV HEADER;
COPY 3

postgres=# SELECT * FROM test_table;
 id | food_name
----+-----------
 1  |  りんご
 2  |  パン
 3  |  ハンバーグ
(3 rows)

無事、取り込むことができました。

COPYコマンドは既存のテーブルにデータを追記していくので、既にレコードが存在していても問題なく取り込むことができます。

$ cat /xxxxx/test_table_add.csv
id,food_name
4,みかん
5,パスタ
6,オムライス

postgres=# COPY test_table (id, food_name) FROM '/xxxxx/test_table_add.csv' WITH CSV HEADER;
COPY 3

postgres=# SELECT * FROM test_table;
 id | food_name
----+-----------
 1  |  りんご
 2  |  パン
 3  |  ハンバーグ
 4  |  みかん
 5  |  パスタ
 6  |  オムライス
(6 rows)

COPY TO

データの書き出しをやってみます。今回は、先ほど投入したデータに対してコマンドを実行します。

postgres=# COPY test_table TO '/xxxxx/output1_test_table.csv' WITH CSV HEADER;
COPY 6

$ cat /xxxxx/output1_test_table.csv
id,food_name
1,りんご
2,パン
3,ハンバーグ
4,みかん
5,パスタ
6,オムライス

また、COPY TO では、テーブル名を指定する代わりに、SELECT文で取得対象を指定することもできます。

postgres=# COPY (SELECT * FROM test_table WHERE id = 4) TO '/xxxxx/output2_test_table.csv' WITH CSV HEADER;
COPY 1

$ cat /xxxxx/output2_test_table.csv
id,food_name
4,みかん

注意事項

\COPY

対象ファイルがDBサーバ内に存在している場合は、COPYコマンドでデータの読み書きを実行できるのですが、ファイルがDBサーバ内に存在せず、クライアント側に存在する場合はCOPYコマンドで実行することはできないようです。
（ちなみに私はここでハマりました。。あくまでCOPYコマンドは、DBサーバ上でファイルを入出力するコマンド。）

クライアント側に読み書き対象ファイルを配置する場合は、\COPYコマンドで実行することができます。

-- ファイルからデータを取り込む（先頭に￥をつけます）
\COPY test_table (id, food_name) FROM '/xxxxx/test_table.csv' WITH CSV HEADER;

-- ファイルにデータを書き出す（先頭に￥をつけます）
\COPY test_table TO '/xxxxx/bk_test_table.csv' WITH CSV HEADER;

ファイルのパスには、クライアント側のパスを絶対パスで指定します。

また、\COPYコマンドはスーパーユーザでなくても実行することができます。

文字コード

読み込み対象のCSVファイルの文字コードが、DBの文字コードと一致していない場合は、ENCODINGオプションを指定する必要があります。

-- DBがUTF8、ファイルがSJISの場合。
COPY test_table (id, food_name) FROM '/xxxxx/test_table.csv' encoding 'sjis' WITH CSV HEADER;

ENCODINGオプションを省略した場合は、クライアントの符号化方式の設定が適用されます。
（クライアント符号化方式はSET client_encoding TOや \encodingコマンドで変更可能）

最後に

備忘録みたいになりましたが、今回はPostgreSQLのCOPYコマンドについて書いてみました。

また、気になるPostgreSQLの機能などを見つけたら、書いてみようと思います。