Postgresqlのコマンドラインツールpsqlには\copyというコマンドがあり、CSVファイルのデータをDBテーブルにインポートできます。しかし、ユニークキー制約や外部キー制約に抵触し、エラーが発生する場合があります。
この記事では、\copyコマンドを使用しつつ、そのようなエラーを回避して新規データのみをインポートする方法を解説します。
目次
\copyコマンドによるCSVインポート- DBテーブル制約によるエラー
- 一時テーブルによるエラー回避
- まとめ
\copyコマンドによるCSVインポート
\copyはpsqlのコマンドで、CSVファイルのインポートに使用できます。使用例は以下の通りです。
# psqlでのインポート
\copy examples FROM 'examples.csv' DELIMITER ',' CSV HEADER;ただし、\copyコマンドはテーブル上の既存データを上書きするため、CSVファイルには既存データも含める必要があります。そのため、最初に\copyコマンドで既存テーブルをCSVファイルにエクスポートし、新規データをCSVに追加してからインポートするという流れが一般的です。
# psqlでのエクスポート
\copy examples TO 'examples.csv' DELIMITER ',' CSV HEADER;DBテーブル制約によるエラー
DBテーブルにプライマリーキーなどのユニークキー制約が存在し、CSVファイルにそのカラムが含まれていると、以下のようなエラーが発生します。
# SQL
ERROR: duplicate key value violates unique constraint "examples_pkey"
DETAIL: Key (id)=(1) already exists.ユニークキー制約によるエラーを回避するには、テーブルレコードを一旦すべて消去すれば良いのですが、テーブルに外部キーが設定されていると、レコード削除自体ができません。
# SQL
DELETE FROM examples;
# エラー
ERROR: update or delete on table "examples" violates foreign key constraint "fk_hogehoge" on table "another_table"
DETAIL: Key (id)=(1) is still referenced from table "another_table".一時テーブルによるエラー回避
このような場合は、一時テーブル（Temporary Table）を経由するとエラーを回避できます。
まず、インポート対象テーブルと同じ構造を持つ一時テーブルを作成します。
# SQL
CREATE TEMP TABLE temp_examples (LIKE examples);次に、\copyで一時テーブルにCSVファイルをインポートします。一時テーブルは空なので、ユニークキー制約や外部キー制約でエラーによるエラーは発生しません。
# psql
\copy temp_examples FROM 'examples.csv' DELIMITER ',' CSV HEADER;最後に、対象テーブルに一時テーブルのデータをコピーします。
# psql
INSERT INTO examples SELECT * FROM temp_examples ON CONFLICT (id) DO NOTHING;ここでポイントとなるのは、ON CONFLICT (id) DO NOTHING句です。これは、idカラムでユニークキー制約に抵触した場合、何もせずスキップし、次のレコードをコピーする、という意味です。つまり、対象テーブルの既存レコードは上書きされずそのまま残り、新規レコードのみが追加されることになります。
なお、CSVファイルに既存レコードを含まず、新規データだけを一時テーブルにインポートした場合、ON CONFLICT (id) DO NOTHING句は不要です。どちらの方法を選んでも結果は同じですので、作業しやすい方を選択してください。
データがすべて正常にインポートされたことを確認したら、psqlを終了して作業完了です。一時テーブルはその名の通り、psqlのセッションが終われば自動的に削除されるため、手動で削除する必要はありません。
まとめ
psqlの\copyコマンドでCSVをインポートする際にユニークキー制約や外部キー制約に抵触してしまう場合、一時テーブルを経由することで制約を回避し、新規データのみをインポートできます。