【PostgreSQL】COPY コマンド ～ COPY FROM / TO ～

PostgreSQL

■ はじめに

https://dk521123.hatenablog.com/entry/2020/02/08/001155
https://dk521123.hatenablog.com/entry/2020/05/08/175525

などで少し触れた PostgreSQL の COPY コマンド だが
Redshift でも使えるみたいなので、まとめる

Amazon Redshift ～ COPY コマンド ～
https://dk521123.hatenablog.com/entry/2021/07/21/214248

目次

【０】公式ドキュメント
【１】COPY FROM (File to DB)
【２】COPY TO (DB to File)
【３】使用上の注意
　１）区切り文字 DELIMITER での特殊文字指定について

【０】公式ドキュメント

PostgreSQL 公式サイト は以下。

https://www.postgresql.jp/document/16/html/sql-copy.html

【１】 COPY FROM (File to DB)

ファイルからテーブルにデータを読み込む

http://everything-you-do-is-practice.blogspot.com/2017/10/postgresql-copy.html

１）構文

-- CSVファイルの入力
COPY [テーブル名] FROM '[入力CSVファイルパス]' WITH CSV

-- 区切り文字を指定した場合
COPY [テーブル名] FROM '[入力CSVファイルパス]'
WITH DELIMITER '<区切り文字>' CSV;

-- NULLを指定した場合
COPY [テーブル名] FROM '[入力CSVファイルパス]'
WITH NULL '<NULLとして扱う文字>' CSV;
-- 例：空白はNULLとして扱う場合 ... WITH NULL ''

２）サンプル

-- 【前提条件】
-- [1] psql のログインしておく
-- [2] 「使用するテーブル」を参考にproducts テーブルを作成しておくこと

COPY products FROM 'C:\\tmp\\input.csv' WITH CSV HEADER QUOTE '"';

入力ファイル (C:\tmp\input.csv) 

id,product_name,price,description,created_at
"101","prod - A","1200","This is a test.","2020-05-08 11:22:22.222"
"102","prod - B","230","Hello, world!","2020-05-08 11:22:22.222"
"103","prod - C","100","This is a test.","2020-05-08 11:22:22.222"
"104","prod - D","1560","","2020-05-08 11:22:22.222"
"105","prod - E","1210","This is a test.","2020-05-08 11:22:22.222"
"106","prod - F","2930",,"2020-05-08 11:22:22.222"

使用するテーブル 

CREATE TABLE "products" (
    "id" INTEGER NOT NULL,
    "product_name" VARCHAR(50) NOT NULL,
    "price" INTEGER NOT NULL,
    "description" VARCHAR(200) NULL DEFAULT NULL,
    "created_at" TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY ("id")
);

動作確認 

SELECT * FROM products WHERE id BETWEEN 100 AND 106;

"101"    "prod - A"   "1200"   "This is a test."    "2020-05-08 11:22:22.222"
"102"    "prod - B"   "230"    "Hello, world!"  "2020-05-08 11:22:22.222"
"103"    "prod - C"   "100"    "This is a test."    "2020-05-08 11:22:22.222"
"104"    "prod - D"   "1560"   ""    "2020-05-08 11:22:22.222"
"105"    "prod - E"   "1210"   "This is a test."    "2020-05-08 11:22:22.222"
"106"    "prod - F"   "2930"   null    "2020-05-08 11:22:22.222"

【２】 COPY TO (DB to File)

テーブルからファイルにデータを出力

https://qiita.com/cyborg__ninja/items/99efcb5b62a4cef2f156

１）構文

COPY [テーブル名] TO '[出力CSVファイルパス]' WITH CSV DELIMITER ',';

-- CSV header 付
COPY [テーブル名] TO '[出力CSVファイルパス]' WITH CSV HEADER;

-- 囲み文字付 ("")
COPY [テーブル名] TO '[出力CSVファイルパス]' WITH CSV FORCE QUOTE *;

２）サンプル

-- 【前提条件】psql のログインしておく

COPY customers TO 'C:\\tmp\\hello.csv' WITH CSV DELIMITER ',' HEADER FORCE QUOTE *;

出力結果 : Windows環境だと、null=>値なし、空文字 => ' ' 

id,customer_no,customer_name,sex,birth_date,created_at
"1","X0001","Mike","m","1972-08-09","2020-05-08 11:22:22.222"
"2","X0002","Smith",,"1972-08-09","2020-05-08 11:22:22.222"
"3","X0003","Naomi"," ","1972-08-09","2020-05-08 11:22:22.222"

【３】使用上の注意

１）区切り文字 DELIMITER での特殊文字指定について

* タブ（\t）などの特殊文字については、E を付けて \ を特殊文字として扱う必要がある
 => そうしないと、PostgreSQL では
　　区切り文字が "\" と "t" を別々に解釈して２文字の区切り文字と認識されるため

例 

COPY products FROM 'C:\\tmp\\input.csv'
WITH CSV DELIMITER E'\t' HEADER QUOTE '"';

２）サポートしているファイル形式

https://www.postgresql.jp/docs/9.4/sql-copy.html

より抜粋
~~~~~~
FORMAT
 読み取りまたは書き込みに使用するデータ形式を選択します。 
 text、csv（カンマ区切り値）、またはbinaryです。 デフォルトはtextです。
~~~~~~

なので、Parquetファイルなどはサポート外。

関連記事

psql で CSV出力する
https://dk521123.hatenablog.com/entry/2020/02/08/001155
CSVデータをPostgreSQLにインポートする ～ copy_from() 編 ～
https://dk521123.hatenablog.com/entry/2020/05/08/175525
Amazon Redshift ～ COPY コマンド ～
https://dk521123.hatenablog.com/entry/2021/07/21/214248
Snowflake と PostgreSQL の違い
https://dk521123.hatenablog.com/entry/2024/09/28/212432

ランキング参加中
プログラミング