PostgreSQL でダブルクォートを含んだフィールドが正しくインポートできない場合

PostgreSQL で CSV ファイルのインポート

PostgreSQL にて CSV(広義の意味の方で、Character Separated Values)からのインポートをする場合に、ダブルクォートが含まれているフィールドがインポートできずにハマった話です*1

概要

例えば以下のようなもともとのテーブルがあったとします。

id name
1 tanaka
2 yamada
3 ho"ge

これをタブ区切りで CSV として出力します(タブを \t として表現しています)。

1\ttanaka
2\tyamada
3\tho"ge

この CSV ファイルを PostgreSQL に普通に COPY でインポートします。

ERROR: unterminated CSV quoted field

まずはこのエラーで怒られます。

ERROR: unterminated CSV quoted field

これは、COPY コマンドはデフォルトで"を引用符として扱っている(らしい)からです。ここで一点注意すべきなのは、行をまたいでダブルクォートを含むフィールドが存在すると、その範囲は「くくられたもの」として扱われてインポートが試みられる(と思われる)点です。したがって、本来の行数よりも少ない行数でインポートが行われ、しかもいい感じで「くくられて」いる場合(ダブルクォートの数が偶数の場合)はエラーを出さずに終了するため、何が原因か気づきにくいままインポートが完了してしまいます。

ダブルクォートをエスケープしてみる

となると当然ダブルクォートをエスケープすればいいという発想に行き着きます。具体的には"""にすればいいという考えです。本来置換すべきでない"に注意して、うまいこと CSV ファイル内の当該ダブルクォートを"から""に置換したとします。インポートをしてみます。エラーが出ず、行数も CSV ファイルに一致しており、うまくいったように見えます。

しかしながらインポートされたデータを見てみると次のようになっています。

id name
1 tanaka
2 yamada
3 hoge

ダブルクォートが消えている……。

この理由は、先ほどの「デフォルトで"を引用符として扱っている」ことから考えれば納得がいくところで、""が要は空の文字を示していると認識されたためと思われます(たぶん)。

それじゃあ"自身をダブルクォートでくくってやるとして、"""と3つつなげればいいのではないか、という発想にも行き着くと思いますが、これだとやはりERROR: unterminated CSV quoted fieldになります。

絶対にフィールドに入るはずのない文字を引用符として指定してインポートする

途方にくれていたところ、こちらのページに解がありました(感謝)。

インポートの際にquoteオプションを用いて「引用符」を指定(変更)してやればいいのです。ただ、その「引用符」がフィールドに含まれる可能性がある文字ですとマズい(結局ダブルクォートが引用符である場合と同じ状況に陥る)ので、「絶対にフィールドに存在し得ない文字」を「引用符」に指定すればいいのです。上記ページでは\x08を「引用符」として指定していますね。

これが良い方法なのかは別として、確かにこの方法でインポートすればエラーも出ず、行数も一致し、ダブルクォートそのものも正しくインポートされます。

pgAdmin III では上記の方法は使えない

なお、pgAdmin III では、quoteオプションを指定する際に強制的に半角1バイト分しか指定できないので、\x08などの文字を指定することはできません。したがって素直にpsqlを使うしかないと思います。

*1:ダブルクォートをフィールドに含むことの是非は置いといて

Powered by はてなブログ