postgresでcopyとupdateを使ってレコード移動をする方法

概要

postgresのcopyとupdateを使って、フィールドが同じテーブル間でレコードを移動する方法です。
postgres9.5からはON CONFLICTを使ってupsertが簡単にできるようになりましたが、
それ以前のバージョンでは、copyやupdateなどを使って、insertとupdateに分けてレコードの移動をする必要があります。

手順

psqlでインポート元のDBにログイン

psql -h ホスト名 -U ユーザー名 -d インポート元DB名

insertしたいレコードをCSVでエクスポート

\copy (SELECT * FROM table_name WHERE created_at > '2017/10/05 00:00:00') TO 'pg_out_insert.csv' WITH CSV HEADER

updateしたいレコードをCSVでエクスポート

\copy (SELECT * FROM table_name WHERE updated_at > '2017/10/05 00:00:00' AND created_at < '2017/10/05 00:00:00') TO 'pg_out_update.csv' WITH CSV HEADER

insertしたいレコードとupdateしたいレコードとを分けてエクスポートしておきます。

psqlでインポート先のDBにログイン

psql -h ホスト名 -U ユーザー名 -d インポート先DB名

insertを実行

\copy table_name FROM 'pg_out_insert.csv' WITH CSV HEADER

updateを実行

-- 一時テーブルを作成
-- データは空でフィールドをコピー
CREATE TEMP TABLE temp_table_name AS
TABLE table_name
WITH NO DATA;

-- 一時テーブルにCSVからインポート
\copy temp_table_name FROM 'pg_out_update.csv' WITH CSV HEADER

-- UPDATEを実行
UPDATE table_name a
SET
(id, hospital_id, ward_type, bed_division, number_of_wards, number_of_beds, division, application_division, created_at, updated_at, acceptance_code)
=
(b.id, b.hospital_id, b.ward_type, b.bed_division, b.number_of_wards, b.number_of_beds, b.division, b.application_division, b.created_at, b.updated_at, b.acceptance_code)
FROM temp_table_name b
WHERE a.id = b.id;

-- セッションが切れれば一時テーブルは自動で削除される

補足

• \copyメタコマンドとCOPYコマンドでは、読み込まれるファイルの場所に違いがあります。\copyではpsqlを実行しているクライアント上のファイルが、COPYではログインしているDBサーバー上のファイルが読み込まれます。

• やはりけっこう面倒なので、ON CONFLICTが使える環境にした方が良いです。