PostgreSQLの「データの中に引用符のない復帰記号がありました」エラーの対処法

on 9月 26, 2020

PostgreSQLにNYCタクシーデータのCSVをインポート

象のロゴでおなじみのPostgreSQL
Daniel Lundin / PostgreSQL License

ビッグデータ解析の練習として、アメリカ、ニューヨークのタクシーデータをこちらのサイトからCSV形式でダウンロードした。CSVでいちいち読み込むのも面倒なので、一旦データベースに保存しようと思い、PostgreSQL 12にインポートすることにした。

pgAdmin4のGUIからCSVファイルを1ファイルずつインポートはできるが、ファイルが多いので、SQLを書いてインポートしようとした。WindowsのバッチファイルでFOR文でpsqlのCOPY FROMを回そうとしたが、謎のエラーに遭遇。エラーが起きたのは、以下のような構文。

psql -h localhost -p 5432 -U USERNAME -d DATABASE_NAME -c "\COPY nyctaxi FROM C:\Data\NYC\yellow_tripdata_2018-03.csv (FORMAT CSV, DELIMITER ',', HEADER)"
Code language: JavaScript (javascript)

エラーメッセージは

ERROR: データの中に引用符のない復帰記号がありました
HINT: 復帰記号を表現するにはCSVフィールドを引用符で括ってください
CONTEXT: nyctaxiのCOPY、行 2

引用符のない復帰記号って何なんだと調べること1時間

最初、psql -cで渡しているSQL構文が悪いのかと思って、色々と変えてみたが、うまくいかない。また、「データの中に引用符のない復帰記号がありました」という日本語エラーメッセージをググっても、有益な情報が全く出てこなかった。やっぱり、プログラミングやIT系の情報を調べるのに、日本語だと情報量が圧倒的に減ってしまう。PostgreSQLのように日本でもよく使われているデータベースでもこんなことがあるんだなと驚いた。

PostgreSQLのエラーメッセージを英語表記に変更

私は普段、WindowsのOSの言語表記は英語(米国)にしているし、ChromeやOutlook、パワーポイント、Visual Studioなどのソフトウェアも、言語が選択できるなら英語表記にしてしまっている。色々とトラブルシューティングするときに、英語のメッセージで調べるほうが早く答えに辿りつけるからだ。顧客向けの資料作成でスクリーンショットを撮る必要があるときは、日本語表記に戻している。

さて、そんな状態で使っていて、コマンドプロンプトのUIも英語になっているのだが、PostgreSQLのエラーメッセージは日本語のままだ。どうやら、PostgreSQLはpostgres.confの設定ファイルから変更できるようだ。$POSTGRESのインストールフォルダ\data\postgresql.confをテキストエディタで編集し、lc_mesagesを変更する。

#lc_messages = 'Japanese_Japan.932'			# locale for system error message
lc_messages = 'en_US.UTF-8'Code language: PHP (php)

そして、Windowsのサービスからpostgesのサービスを再起動すると、エラーメッセージが英語表記に変わった。

先程のエラーメッセージはこうなった。

ERROR: unquoted carriage return found in data
HINT: Use quoted CSV field to represent carriage return.
CONTEXT: COPY nyctaxi, line 2

CSVファイルに空の行がある場合にこのエラーが起こる

英語のエラーメッセージでググってみると、CSVファイルに\n以外のコードの改行があるのが原因らしい。そうか、SQL構文が悪いのではなく、データファイルのほうが原因だったのか。CSVファイルを見てみると、

確かに、2行目が空の行になっている。ニューヨークのタクシーデータは、時期によってデータフォーマットが違っていたり、ファイルサイズが全然違ったりしたが、なぜか2018年のCSVデータには空白行がある。ちゃんとフォーマット決めて欲しいのだが、こういう前処理も含めてビッグデータ解析の練習なんだよ、というメッセージが込められているのだろうか。