PostgreSQLでCSVファイルをレコードに取り込む(COPY編)
COPY 句
ファイル ⇔ テーブル間のデータを高速にコピーする際に使用する。
ファイルの内容を無条件でインポートする際は高速で楽なのだが、あまり複雑なことができず、以下のケースで困る。
- 任意の条件によるインポート対象の絞り込み
- キー重複
COPYコマンドを使用する場合は、上述のようなファイル・データが扱われないよう調整するのが最初の手になる。
1. については、テンポラリテーブルを作成 → INSERT SELECT コマンドの発行(＋テンポラリテーブルの破棄)
2.も同時に対応する場合はON CONFLICTコマンドを追加してやればよい。
2. のみの場合について、
レプリケーション環境でなければpg_bulkloadを使えばよい。
DBなので微妙なライン。。
レプリケーション環境である場合は、file_fdwを使用して外部テーブル化し、INSERT INTO SELECT で ON CONFLICTコマンドを発行する。
- CSVファイルで受け取ったデータを、一括でテーブルに登録する
- テーブルから全レコードのデータをファイル出力する
といったユースケースにおいて、プログラム＋INSERT、SELECT句を使用するよりも簡単かつ高速に実現できるコマンドだ。
テーブル ⇒ ファイル
一番シンプルなパターンだと、
COPY [テーブル名] TO 出力先ファイルパス
で、テーブルの全カラム全レコードがタブ区切りで出力される
ファイル ⇒ テーブル
同じく、まずは一番シンプルに
COPY [テーブル名] FROM 入力元ファイルパス
で、ファイルの全データがインポートされる。
ファイルのデータ順とテーブルのカラム順が一致していなかったり、特定のカラムだけ取り込みを行いたい場合は、テーブル名の後ろにカラム名を指定すればできます。
注意点
ファイルパスはPostgreSQLが参照可能なディレクトリである必要があります。
DBとクライアントとなるアプリがそれぞれ別サーバに配置されている場合、指定されるファイルパスをDBサーバが参照可能なディレクトリとする必要があります。
上記構成の場合、DBサーバとアプリサーバで共通で参照可能なディレクトリを共有しておく必要があります。
