PostgreSQLのCOPYコマンド：データ移行の強い味方！基本から応用まで徹底解説

2024-04-18

PostgreSQLでCOPYコマンドを使ってテーブル間でデータをコピーする方法

基本的な構文

COPY table_name (column1, column2, ...)
FROM 'file_path'
[DELIMITER delimiter]
[OPTIONS];

table_name: データをコピーする先のテーブルの名前
column1, column2, ...: コピーする列の名前。省略可。指定しない場合は、すべての列がコピーされます。
'file_path': データの入ったファイルのパス
DELIMITER delimiter: ファイル内のデータ区切り文字。デフォルトはカンマ（,）
OPTIONS: オプション。省略可。

例:

COPY users (user_id, name, email)
FROM '/path/to/data.csv'
DELIMITER ','
OPTIONS (FORMAT 'csv');

この例では、/path/to/data.csv というCSVファイルにあるデータが、usersテーブルにコピーされます。CSVファイルの区切り文字はカンマ（,）です。

オプション

COPYコマンドには、さまざまなオプションが用意されています。主なオプションは以下の通りです。

STDIN: データソースを標準入力に指定
FORMAT: データ形式を指定（csv、textなど）
NULL AS: NULL値の表現方法を指定
ESCAPE: エスケープ文字を指定

オプションの詳細については、PostgreSQLのドキュメントを参照してください: https://www.postgresql.org/docs/current/sql-copy.html

データの書き出し

テーブルの内容をファイルに書き出すには、COPY TOコマンドを使用します。

COPY table_name (column1, column2, ...)
TO 'file_path'
[DELIMITER delimiter]
[OPTIONS];

COPY users (user_id, name, email)
TO '/path/to/output.csv'
DELIMITER ','
OPTIONS (FORMAT 'csv');

この例では、usersテーブルの内容が、/path/to/output.csv というCSVファイルに出力されます。

COPYコマンドは、大規模なデータの移行にも有効です。
COPYコマンドは、トランザクション内で実行できます。

COPYコマンドは、PostgreSQLでテーブル間でデータをコピーする際に便利な機能です。基本的な構文を理解すれば、簡単にデータ移行を行うことができます。

PostgreSQLでCOPYコマンドを使う際のサンプルコード

CSVファイルからテーブルへのデータ読み込み

この例では、users.csvというCSVファイルからデータをusersテーブルに読み込みます。CSVファイルの形式は次のとおりです。

user_id,name,email
1,Taro Yamada,[email protected]
2,Hanako Sato,[email protected]
3,Jiro Tanaka,[email protected]

コード:

COPY users (user_id, name, email)
FROM '/path/to/users.csv'
DELIMITER ','
OPTIONS (FORMAT 'csv');

説明:

users (user_id, name, email): コピーする列を指定しています。
FROM '/path/to/users.csv': データソースとなるCSVファイルを指定しています。
DELIMITER ',': CSVファイルの区切り文字をカンマ（,）に指定しています。
OPTIONS (FORMAT 'csv'): データ形式をCSVに指定しています。

テーブルからCSVファイルへのデータ書き出し

COPY users (user_id, name, email)
TO '/path/to/users.csv'
DELIMITER ','
OPTIONS (FORMAT 'csv');

特定の列のみをコピー

COPY (user_id, name)
FROM users
TO '/path/to/users_id_name.csv'
DELIMITER ','
OPTIONS (FORMAT 'csv');

FROM users: データソースとなるテーブルを指定しています。

NULL値の表現方法を指定

この例では、usersテーブルのemail列のNULL値を空文字（''）としてCSVファイルに書き出します。

COPY users (user_id, name, email)
TO '/path/to/users.csv'
DELIMITER ','
OPTIONS (FORMAT 'csv', NULL AS '');

NULL AS '': NULL値を空文字（''）として表現することを指定しています。

大規模なデータの移行

COPYコマンドは、大規模なデータの移行にも有効です。以下の点に注意することで、効率的にデータ移行を行うことができます。

インデックスの無効化: データの読み書き中はインデックスが不要になるため、無効化することでパフォーマンスを向上させることができます。
バッファサイズの調整: SET statement_buffer_size = <size> コマンドでバッファサイズを調整することで、一度に読み書きするデータ量を増やすことができます。
圧縮の使用: COPY ... WITH (COMPRESS LEVEL <level>) オプションを使用することで、データ転送時の圧縮を行うことができます。

これらのサンプルコードを参考に、状況に合わせて様々なバリエーションでCOPYコマンドを活用してみてください。

INSERT INTO SELECT構文:

この構文は、シンプルなデータコピーや、SELECT句で条件を絞ったデータコピーに適しています。

INSERT INTO target_table (column1, column2, ...)
SELECT column1, column2, ...
FROM source_table
[WHERE condition];

INSERT INTO users_copy (user_id, name, email)
SELECT user_id, name, email
FROM users
WHERE created_at > '2024-01-01';

利点:

シンプルでわかりやすい構文
SELECT句で条件を絞ったデータコピーが可能

大規模なデータコピーには不向き
トランザクション境界を越えたコピーはできない

CREATE TABLE AS構文:

この構文は、既存のテーブルの構造とデータを新しいテーブルにコピーする場合に適しています。

CREATE TABLE target_table AS
SELECT * FROM source_table
[WHERE condition];

CREATE TABLE users_archive AS
SELECT * FROM users
WHERE created_at < '2024-01-01';

既存のテーブルの構造をそのままコピーできる

INSERT INTO SELECT構文に比べて処理速度が遅い場合がある

pg_dumpとpg_restoreコマンド:

この方法は、データベース全体や複数のテーブルをまとめてコピーする場合に適しています。

pg_dump: データベースまたはテーブルをダンプファイルにバックアップ
pg_restore: ダンプファイルからデータベースまたはテーブルを復元

# データベース全体をダンプ
pg_dump -d my_database > my_database.dump

# 特定のテーブルをダンプ
pg_dump -d my_database -t users users.dump

# ダンプからデータベースを復元
pg_restore -d my_database_copy my_database.dump

# ダンプからテーブルを復元
pg_restore -d my_database users.dump