postgresqlでデータの流し込み

データを流し込む方法として、ダンプ/リストアを使う方法と、エクスポート/インポートを使う方法をメモ。

ダンプ＆リストア

# ダンプ
# テーブル定義のみダンプしたい場合は「-s」、データのみダンプしたい場合は「-a」を付ける
# 下記コマンドは、データのみダンプする場合
pg_dump [-h ホスト名] -U ユーザ名 -a -t テーブル名 データベース名 > data.sql

# 適時ダンプファイルを修正（ユーザ名の置換とか）

# リストア
# 単一トランザクション内でスクリプトを実行したい場合は「-1」を付ける
psql [-h ホスト名] -d データベース名 -U ユーザ名 -f data.sql -1

ちなみに、psqlはSQLファイルを実行するコマンドなので、下記のようなSQLファイルを別途用意してもいい。

create table city (
	city_id serial PRIMARY KEY NOT NULL,
	country_code text NOT NULL,
	pref_code text NOT NULL,
	pref_name text NOT NULL,
	city_code text NOT NULL,
	city_name text NOT NULL
);

(略)

エクスポート＆インポート

# エクスポート
psql [-h ホスト名] -d データベース名 -U ユーザ名 -A -F, -c "select * from テーブル名" > data.csv
psql [-h ホスト名] -d データベース名 -U ユーザ名 -A -F $'\t' -c "select * from テーブル名" > data.tsv

# csv/tsvファイルの編集（data.csv/tsvのカラム名/レコード数に関する記述を削除）

# インポート
# コマンド文字列（「-c」に続く文字列）が複数のSQLコマンドを含む場合、それらのコマンドは1つのトランザクションで処理される 
# トランザクションを複数に分ける場合は、BEGIN/COMMITコマンドを明示的に追記する
psql [-h ホスト名] -d データベース名 -U ユーザ名 -c "\copy テーブル名 (カラム1, カラム2, ...) from data.csv with delimiter ','"
psql [-h ホスト名] -d データベース名 -U ユーザ名 -c "\copy テーブル名 (カラム1, カラム2, ...) from data.tsv with delimiter '（ターミナルで「Ctrl + v + Tab」）'"