PostgreSQLでファイルからのデータ取込

time 2018/02/11

folder PostgreSQL

本記事では、PostgreSQLにおいてファイルからDBのテーブルにデータを取り込む方法について、まとめました。

ファイルデータの取込は、他システムとの連携等でよく使われます。

メタコマンド¥copy

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

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

使用方法

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

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

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

-- ファイルデータ取込
¥copy test1 from '/xxx/test1.log' with csv

-- テーブルデータ
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)

-- 取込ファイル内容

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

2,1,00002,,2639

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

-- ファイルデータ取込

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

-- テーブルデータ

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(c1,c4,c5) from '/xxx/test1.log' with csv

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

-- ファイルデータ取込（取込項目を指定）

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

-- テーブルデータ

SELECT * FROM test1;

c1 | c2 | c3 | c4 | c5

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

1 | | | 2018-01-01 | 7628

2 | | | | 2639

3 | | | 2018-01-03 |

(3 rows)

タブ区切りのファイルからデータを取り込む
区切り文字を変更することも可能です。
区切り文字を変更には、delimiter ‘x’（任意の1バイト文字）のオプションをつけます。
タブ区切りにするには、delimiter E’¥t’とします。

-- ファイルデータ取込（タブ区切り）
¥copy test1 from '/xxx/test1.log' with csv delimiter E'¥t'

1 2	-- ファイルデータ取込（タブ区切り） ¥copy test1 from '/xxx/test1.log' with csv delimiter E'¥t'

ヘッダーつきのファイルからデータを取り込む
ヘッダーつきのファイルからデータを取り込むことも可能です。
headerのオプションをつけることで1行目の行を無視して取り込みます。

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

-- ファイルデータ取込（ヘッダーつきファイル）
¥copy test1 from '/xxx/test1.log' with csv header

-- テーブルデータ
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)

-- 取込ファイル内容

c1,c2,c3,c4,c5

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

2,1,00002,,2639

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

-- ファイルデータ取込（ヘッダーつきファイル）

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

-- テーブルデータ

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コマンド

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

使用方法

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

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

1点目は、データベースのスーパーユーザのみコマンドの実行が可能という点です。
2点目は、サーバーの環境のファイルを取り込むという点です。クライアントから接続している場合でも、サーバー環境のファイルから取込を行います。（パスもサーバーのパスを記述する、絶対パス指定必須）

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

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

-- ファイルデータ取込
COPY test1 FROM '/xxx/test1.log' WITH CSV

-- テーブルデータ
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 FROM '/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.