【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)

■ PostgreSQL 公式サイト

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

【１】 COPY FROM (File to DB)

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

構文

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

サンプル

# psql のログインしておく
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"

参考文献

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

【２】 COPY TO (DB to File)

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

構文

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"

参考文献

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

関連記事

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