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:ダブルクォートをフィールドに含むことの是非は置いといて