PostgreSQL コマンド COPY — ファイルとテーブルの間でデータをコピーする

初心者が公式ドキュメントと格闘するシリーズ(勝手に始めます)

初心者にはハードルの高い公式ドキュメント、しかし早めに読めるようになっておきたいものです。そこで、SQLの学習開始を機に公式ドキュメントとの格闘を始めます。
まず最初は、コマンド COPY — ファイルとテーブルの間でデータをコピーするに挑戦します!

コマンド COPY — ファイルとテーブルの間でデータをコピーする(PostgreSQL 11.5文書)

筆者の初心者具合: JOINブログにこれまでの経歴や初心者具合も書いております。
それでは、公式ドキュメントに沿って進みます。

概要

こちらがラスボスですね。本ブログはココと格闘する様をお届けいたします。


COPY table_name [ ( column_name [, ...] ) ]
    FROM { 'filename' | PROGRAM 'command' | STDIN }
    [ [ WITH ] ( option [, ...] ) ]

COPY { table_name [ ( column_name [, ...] ) ] | ( query ) }
    TO { 'filename' | PROGRAM 'command' | STDOUT }
    [ [ WITH ] ( option [, ...] ) ]


ここでoptionは以下のいずれかです。

    FORMAT format_name
    OIDS [ boolean ]
    FREEZE [ boolean ]
    DELIMITER 'delimiter_character'
    NULL 'null_string'
    HEADER [ boolean ]
    QUOTE 'quote_character'
    ESCAPE 'escape_character'
    FORCE_QUOTE { ( column_name [, ...] ) | * }
    FORCE_NOT_NULL ( column_name [, ...] )
    FORCE_NULL ( column_name [, ...] )
    ENCODING 'encoding_name'


説明

COPYコマンドは大きく2種類のやりとりをします。

  • COPY FROM: ファイルをテーブルに読み込むコマンド
  • COPY TO: テーブルからファイルへ書き出すコマンド
  • また、COPY TOコマンドは書き出しの際にSELECT文で書き出す値を指定できるようです。

上記は、COPYコマンドはどういうものなのかについて、自分の言葉で言い換えたものです。
本物はこちらです。

COPYコマンドは、PostgreSQLのテーブルと標準のファイルシステムのファイル間でデータを移動します。 COPY TOコマンドはテーブルの内容をファイルにコピーします。 また、COPY FROMコマンドは、ファイルからテーブルへとデータをコピーします(この時、既にテーブルにあるデータにコピーした内容を追加します)。 また、COPY TOによりSELECT問い合わせの結果をコピーすることができます。

列リストが指定されている場合、COPYは、指定された列のデータのみをファイルからまたはファイルへコピーします。 列リストに含まれていない列がテーブル内にある場合、COPY FROMはそれらの列にデフォルトの値を挿入します。

ファイル名付きのCOPYコマンドは、PostgreSQLサーバに対して直接ファイルへの読み書きをするように命じます。 指定したファイルは必ずPostgreSQLユーザ(サーバを実行しているユーザID)からアクセスできる必要があります。 また、ファイル名はサーバから見たように指定されなければなりません。 PROGRAMが指定された場合、サーバは指定したコマンドを実行しその標準出力を読み取る、または、プログラムの標準入力に書き出します。 コマンドはサーバからの視点で指定しなければならず、また、PostgreSQLユーザによって実行できなければなりません。 STDINやSTDOUTが指定された場合、データはクライアントとサーバ間を流れます。

英語の文章を翻訳した文章ということもあるのでしょうが、読み進めて間も無く私の頭の中は読んでいても理解はしていないモードに切り替わりました。
ここで躓くわけにはいかないので、他の部分については必要に応じて読み解くとして、早速実際の値を入れて「概要」さま、「説明」さまとお近づきになりましょう。

実行環境

macOS Catalina 10.15.3
PostgreSQL 12.1

実践(初歩編)

COPY TO

概要にはなんやかんや書いてありますが、[]はあってもいいですよ、{}は|がorの意味なので、{}のなかのどれか選べますよということだろうという想定のもと、まずはシンプルにCOPY table_name FROM 'filename'で実行してみます。

事前に用意しておいたpeopleテーブルを使用します。

postgres=# select * from people;
  name  |  birthday  |  city  |         email         
--------+------------+--------+-----------------------
 Harry  | 1980-07-31 | Godric | hello_harry@mail.com
 Eddie  | 1982-01-06 | London | hello_eddie@mail.com
 Helena | 1966-05-26 | London | hello_helena@mail.com
 Hannah | 1982-11-30 | London | hello_hannah@mail.com
(4 rows)

COPY table_name FROM file_nametable_nameにpeopleを、'filename'に'people.txt'を入れて書き出します。

postgres=# COPY people
postgres-# TO 'people.txt';
ERROR:  relative path not allowed for COPY to file

早速エラーです。
相対パスはダメ = 絶対パスじゃなきゃイヤ! と言われてしまいましたので、絶対パスでデスクトップにファイルを置くよう指定します。

postgres=# COPY people
TO '/Users/user_name/Desktop/people.txt';
COPY 4

無事デスクトップに書き出されました!

ファイル名の指定についてですが、パラメータのfilenameにきちんと書いてありました。

入出力ファイルのパス名です。 入力ファイル名は絶対パスでも相対パスでも記述することができますが、出力ファイル名は絶対パスでなければなりません。 Windowsユーザの場合、E''文字列を使用し、パス名内のバックスラッシュを二重にする必要があるかもしれません。

Windows システムのファイル パス形式 - Microsoft
Windowsの場合にはパスの記述方法が違うそうです。参考になりそうなサイトを貼っておきます。

Python, pathlibで絶対パスと相対パスを相互変換・判定
Pythonのモジュールの解説をしているページですが、絶対パスと相対パスについて解説があるので、わからない方は読んでみてください。

COPY FROM

次は書き出したファイルをCOPY table_name FROM file_nameで読み込みます。

postgres=# COPY people_from
postgres-# FROM '/Users/user_name/Desktop/people.txt';
ERROR:  relation "people_from" does not exist

またエラーです。
先ほどのテーブルとは別のテーブルにしようと思い、テーブル名をpeople_fromとしたのですが、そんなテーブルはないと言われてしまいました。
コピー先のテーブルが先に必要ということですね。people_fromテーブルを作成します。

postgres=# CREATE TABLE people_from (
name CHARACTER VARYING(50),
birthday DATE,
city character varying(50),
email character varying(50) PRIMARY KEY);
CREATE TABLE
postgres=# SELECT * FROM people_from;
 name | birthday | city | email 
------+----------+------+-------
(0 rows)

このpeople_fromテーブルにCOPY FROMします。

postgres=# COPY people_from
FROM '/Users/user_name/Desktop/people.txt';
COPY 4

無事、読み込めました!

postgres=# SELECT * FROM people_from;
  name  |  birthday  |  city  |         email         
--------+------------+--------+-----------------------
 Harry  | 1980-07-31 | Godric | hello_harry@mail.com
 Eddie  | 1982-01-06 | London | hello_eddie@mail.com
 Helena | 1966-05-26 | London | hello_helena@mail.com
 Hannah | 1982-11-30 | London | hello_hannah@mail.com
(4 rows)

テーブル指定について「説明」に書いてありました。

COPY FROMコマンドは、ファイルからテーブルへとデータをコピーします(この時、既にテーブルにあるデータにコピーした内容を追加します)。

それでは、すでに値が入っているpeopleテーブルにCOPY FROMしたらどうなるか試してみます。

postgres=# COPY people     
FROM '/Users/user_name/Desktop/people.txt';

実行!

ERROR:  duplicate key value violates unique constraint "people_pkey"
DETAIL:  Key (email)=(hello_harry@mail.com) already exists.
CONTEXT:  COPY people, line 1

テーブルを作る際にemailをprimary keyに指定していたため、コピーを試みた値と元の値が競合しているとエラー出てきました。
では被らない場合はどうなのか、ファイルをコピーしてemailを書き換えて試してみます。

postgres=# COPY people
FROM '/Users/user_name/Desktop/people2.txt';
COPY 4
postgres=# SELECT * FROM people;
  name  |  birthday  |  city  |         email          
--------+------------+--------+------------------------
 Harry  | 1980-07-31 | Godric | hello_harry@mail.com
 Eddie  | 1982-01-06 | London | hello_eddie@mail.com
 Helena | 1966-05-26 | London | hello_helena@mail.com
 Hannah | 1982-11-30 | London | hello_hannah@mail.com
 Harry  | 1980-07-31 | Godric | hello_harry@email.com
 Eddie  | 1982-01-06 | London | hello_eddie@email.com
 Helena | 1966-05-26 | London | hello_helena@email.com
 Hannah | 1982-11-30 | London | hello_hannah@email.com
(8 rows)

既存のテーブルに値が追加される形でコピーされました!

次は、先ほどの説明の引用で、読み込みの際には相対パスも指定可能と書いてありましたので、相対パスで読み込んでみます。

その前に、今どこにいるのかを把握する必要がありますが、普通にpwdと入力すると、

postgres=# pwd
postgres-# ;
ERROR:  syntax error at or near "pwd"
LINE 1: pwd

やはりエラーが出ます。調べたところ¥! pwd のように、¥(バックスラッシュ)!でエスケープすればコマンドが使えるみたいです。
PostgreSQL 7.2.3 リファレンスマニュアル

postgres=# \! pwd
/Users/user_name

postgres=# \cd Desktop/
postgres=# \! ls
people.txt

Desktopに people.txt があることも確認できたので、そのように相対パスを記述しましょう。(カレントディレクトリは、ホームディレクトリに戻しました。)

postgres=# COPY people_from
FROM '/Desktop/people.txt';
ERROR:  could not open file "/Desktop/people.txt" for reading: No such file or directory
HINT:  COPY FROM instructs the PostgreSQL server process to read a file. You may want a client-side facility such as psql's \copy.

エラーが出てしまいました。 No such file or directory と言っていますが、上述の通りファイルは Desktopにあります。どういうことでしょうか。

ドキュメントを確認したところ、パスについて別の記述がありました。

クライアントの作業ディレクトリではなく、サーバプロセスの作業ディレクトリ(通常はクラスタのデータディレクトリ)からの相対的なディレクトリとして解釈されます。

pwdで示されるディレクトリからの相対パスではないということですね。色々と格闘した結果、PostgerSQLのあるディレクトリを探し出しました。

name postgres % pwd
/usr/local/var/postgres

ここからの相対パスを記述して実行します。

postgres=# COPY people_from
postgres-# FROM '../../../../Users/user_name/Desktop/people.txt';

すると、下の画像がポップアウトで出現したので、OKを押して再実行!
がしかし...

ERROR:  could not open file "../../../../Users/user_name/Desktop/people.txt" for reading: Interrupted system call

システムコールが邪魔されたと、エラーになってしまいました。 これについてネットで検索をかけてみたのですが、いまいち原因がわかりませんでした。そして、冗談のつもりでしれっともう一度実行してみたらできるんじゃないかと思い実行したところ、何事もなく読み込めました!今回は先ほど出てきたアクセス確認のポップアップも出てきませんでした。(そしてpeople_fromテーブルは、絶対パスで読み込んだ値をdrop tableしてあります。)

postgres=# COPY people_from
FROM '../../../../Users/user_name/Desktop/people.txt';
COPY 4

postgres=# SELECT * FROM people_from;
  name  |  birthday  |  city  |         email         
--------+------------+--------+-----------------------
 Harry  | 1980-07-31 | Godric | hello_harry@mail.com
 Eddie  | 1982-01-06 | London | hello_eddie@mail.com
 Helena | 1966-05-26 | London | hello_helena@mail.com
 Hannah | 1982-11-30 | London | hello_hannah@mail.com
(4 rows)

あのエラーは何だったのだろう?という疑問はさておき、一番シンプルな記述での書き出しと読み込みは成功したので、次は各パラメータの説明を読んでみます。

パラメータ(実践 基本編)

table_name

既存のテーブルの名前です(スキーマ修飾名も可)。

スキーマとは何でしょうか? このメージが参考になったのでリンクを貼っておきます:
PostgreSQLにおけるデータベース、スキーマ、テーブルの関係
スキーマなんて設定した覚えないなと思っていたのですが、デフォルトはPublicだったのですね。

先人のアウトプットに感謝しながら、スキーマの確認コマンドを打ち込んでみます。
[PostgreSQL] よく使うコマンドまとめ

postgres=# \d people_from
                    Table "public.people_from"
  Column  |         Type          | Collation | Nullable | Default 
----------+-----------------------+-----------+----------+---------
 name     | character varying(50) |           |          | 
 birthday | date                  |           |          | 
 city     | character varying(50) |           |          | 
 email    | character varying(50) |           | not null | 
Indexes:
    "people_from_pkey" PRIMARY KEY, btree (email)

確かに、Table "public.people_from"となっていますね。それではこれがスキーマ修飾名だと思うので、こちらでCOPY TOとCOPY FROMを試してみます。

postgres=# COPY public.people_from
TO '/Users/user_name/Desktop/people_schema.txt';
COPY 4

はい、何事もなく書き出せました。では次はCOPY FROMをしてみます。

postgres=# COPY public.people_schema
FROM '/Users/user_name/Desktop/people_schema.txt';
COPY 4

postgres=# SELECT * FROM people_schema;
  name  |  birthday  |  city  |         email         
--------+------------+--------+-----------------------
 Harry  | 1980-07-31 | Godric | hello_harry@mail.com
 Eddie  | 1982-01-06 | London | hello_eddie@mail.com
 Helena | 1966-05-26 | London | hello_helena@mail.com
 Hannah | 1982-11-30 | London | hello_hannah@mail.com
(4 rows)

こちらも何事もなくという感じですね。順調ですね!次のパラメーターに参りましょう。

column_name

コピー対象の列リストで、省略可能です。 列リストが指定されていない場合は、テーブルの全ての列がコピーされます。

また、列リストについては「説明」の項にもこのように書いてあります。

列リストが指定されている場合、COPYは、指定された列のデータのみをファイルからまたはファイルへコピーします。 列リストに含まれていない列がテーブル内にある場合、COPY FROMはそれらの列にデフォルトの値を挿入します。

nameとemailを指定して書き出します。

postgres=# COPY people_from (name, email)
TO '/Users/user_name/Desktop/people_selcol.txt';
COPY 4

成功!

nameとemailだけ書き出されています。
次は列を指定して読み込みます。

postgres=# COPY people_selcol (name, email)
FROM '/Users/user_name/Desktop/people.txt';
ERROR:  extra data after last expected column
CONTEXT:  COPY people_selcol, line 1: "Harry   1980-07-31  Godric  hello_harry@mail.com"

エラーですね。「列リストに含まれていない列がテーブル内にある場合、COPY FROMはそれらの列にデフォルトの値を挿入します。」というのはデフォルトの値を指定する必要があるのでしょうか。

postgres=# ALTER TABLE people_selcol ALTER COLUMN birthday SET DEFAULT '1990-01-01';
ALTER TABLE
postgres=# ALTER TABLE people_selcol ALTER COLUMN city SET DEFAULT 'earth';
ALTER TABLE

読み込まない列にデフォルト値を指定しました。もう一度読み込んでみます。

postgres=# COPY people_selcol (name, email)
FROM '/Users/user_name/Desktop/people.txt';
ERROR:  extra data after last expected column
CONTEXT:  COPY people_selcol, line 1: "Harry   1980-07-31  Godric  hello_harry@mail.com"

先ほどと全く同じエラーないようですね。というより、列で指定していない値も読み込んでいますね。
改めてテキストファイルを見ると、列名の記載がどこにもありません。これではどの列を読み込めばいいのかがわかりませんね。また、CSVファイルなら列名も一緒に書き出されるのでできるのではと試行錯誤してみたのですが、この記事を見つけてCOPY FROMでの列名指定での読み込みは断念することにしました。
extra data after last expected column” while trying to import a csv file into postgresql

さて、気を取り直して次に参りましょう。

query

SELECT、VALUES、INSERT、UPDATEあるいはDELETEコマンドで、その結果がコピーされます。 問い合わせを括弧でくくる必要があることに注意してください。 INSERT、UPDATEおよびDELETEについてはRETURNING句を付けなければならず、また、対象のリレーションには、複数の文に展開される条件付きルール、ALSOルール、INSTEADルールがあってはなりません。

よくわかりませんが、慣れ親しんでいるSELECTを使ってnameが'H'で始まる行を抽出して書き出してみます。

元のテーブルです。

postgres=# SELECT * FROM people;
  name  |  birthday  |  city  |         email          
--------+------------+--------+------------------------
 Harry  | 1980-07-31 | Godric | hello_harry@mail.com
 Eddie  | 1982-01-06 | London | hello_eddie@mail.com
 Helena | 1966-05-26 | London | hello_helena@mail.com
 Hannah | 1982-11-30 | London | hello_hannah@mail.com
 Harry  | 1980-07-31 | Godric | hello_harry@email.com
 Eddie  | 1982-01-06 | London | hello_eddie@email.com
 Helena | 1966-05-26 | London | hello_helena@email.com
 Hannah | 1982-11-30 | London | hello_hannah@email.com
(8 rows)

実行!

postgres=# COPY (SELECT * FROM people WHERE name LIKE 'H%')
TO '/Users/user_name/Desktop/people_query.txt';
COPY 6

6件のデータが書き出されました。

幸先がいいですね。次はDELETEしてみます。

postgres=# COPY (DELETE FROM people WHERE birthday = '1982-01-06')
postgres-# TO '/Users/user_name/Desktop/people_query.txt';
ERROR:  COPY query must have a RETURNING clause

はい、エラーです。こちら、公式ドキュメントにきちんと書いてありました。

INSERT、UPDATEおよびDELETEについてはRETURNING句を付けなければならず、・・・

6.4. 更新された行のデータを返す

RETURNING *をつければ良さそうですね。

postgres=# COPY (DELETE FROM people WHERE birthday = '1982-01-06' RETURNING *)
TO '/Users/user_name/Desktop/people_query.txt';
COPY 2

COPY 2とな?

DELETによって削除した2行を書き出すコマンドだったんですね。てっきり指定した行以外の行が書き出されると思っていました。

postgres=# DELETE FROM people WHERE birthday = '1982-01-06' RETURNING *;
 name  |  birthday  |  city  |         email         
-------+------------+--------+-----------------------
 Eddie | 1982-01-06 | London | hello_eddie@mail.com
 Eddie | 1982-01-06 | London | hello_eddie@email.com
(2 rows)

COPY TOをつけずに実行すると、ターミナル上に削除した2行が返されました。この2行をファイルに書き出す作業をしていたのですね。 そうすると、INSERT INTOやUPDATEはどのような動作をするのでしょうか。まずはINSERT INTOから試してみます。

COPY (INSERT INTO people (name, birthday, city, email) 
VALUES ('Eddie', '1982-01-06', 'London', 'hello_eddie@mail.com') RETURNING *)
postgres-# TO '/Users/user_name/Desktop/people_query.txt';
COPY 1

INSERT INTOした1行がファイルに書き出され、peopleテーブルにも挿入されました。

postgres=# select * from people;
  name  |  birthday  |  city  |         email          
--------+------------+--------+------------------------
 Harry  | 1980-07-31 | Godric | hello_harry@mail.com
 Helena | 1966-05-26 | London | hello_helena@mail.com
 Hannah | 1982-11-30 | London | hello_hannah@mail.com
 Harry  | 1980-07-31 | Godric | hello_harry@email.com
 Helena | 1966-05-26 | London | hello_helena@email.com
 Hannah | 1982-11-30 | London | hello_hannah@email.com
 Eddie  | 1982-11-06 | London | hello_eddie@mail.com
(7 rows)

次はUPDATEです。先ほど挿入したEddieの誕生日が間違っているので、正しいものに更新します。

postgres=# COPY (UPDATE people set birthday = '1982-01-06' WHERE birthday = '1982-11-06' RETURNING *)
postgres-# TO '/Users/user_name/Desktop/people_query.txt';
COPY 1

やはり、UPDATEした1行が書き出されています。また、テーブルも更新されています。

postgres=# SELECT * FROM people;
  name  |  birthday  |  city  |         email          
--------+------------+--------+------------------------
 Harry  | 1980-07-31 | Godric | hello_harry@mail.com
 Helena | 1966-05-26 | London | hello_helena@mail.com
 Hannah | 1982-11-30 | London | hello_hannah@mail.com
 Harry  | 1980-07-31 | Godric | hello_harry@email.com
 Helena | 1966-05-26 | London | hello_helena@email.com
 Hannah | 1982-11-30 | London | hello_hannah@email.com
 Eddie  | 1982-01-06 | London | hello_eddie@mail.com
(7 rows)

まとめ

全パラメーターを試そうと思って始めましたが、断念。今回はテキストファイルを使用しましたが、CSVやTSVファイルなども対応しています。その際には書き出されたデバイスによってエンコードを指定したり、どの文字で区切られているのかをDELIMITERで指定したりする必要があって大変そうです。テキストファイルを選んだ理由は、ドキュメントにデフォルトはtxtですと書いてあったためです。

ポイントは最初にテーブルを用意しておくこと!でしょうか。pandasでread_csvを繰り返していた人間からするとつまづきポイントでした。 想像通りの大苦戦でしたが、次回もめげずに公式ドキュメントを読んだ結果をお届けしたいです。

言い回しや、認識違いなど気になる点がありましたら、指摘してください。ありがとうございました!