PostgreSQLのcopyではまった

PostgreSQLのcopyではまったのでメモしておく。

環境はWindows7 64bit/32bit、PostgreSQLは9.4.4。下記のような商品マスターっぽいtsvデータをcopyすると

id_item	name_item
111111	hogehoge ¥200
222222	fugafuga ¥300

copy table_item from 'item.tsv' encoding 'sjis';

ERROR: invalid byte sequence for encoding "UTF8": 0x80
SQLステート:22021
コンテキスト:COPY table_item, line 1: "111111	hogehoge \200"

ということは、¥ → ¥¥ にすれば良いんだね。

id_item	name_item
111111	hogehoge ¥¥200
222222	fugafuga ¥¥300

みたいにすれば当然OK

しかし、今更データいじりたくないので調べるとこんな記事がstackoverflow.com

with で csv形式のコピーにしといて、デリミタでタブを指定です。

copy table_item from 'item.tsv' with csv delimiter e'\t' encoding 'sjis';

クエリーは、成功しました:  2 行の影響があり, 実行時間は、11 ミリ秒でした。

これでうまくいくのですが、まだ問題が！
データ中に引用符「"」があると。。。

id_item	name_item
111111	hogehoge" ¥200
222222	fugafuga" ¥300

Query returned successfully: one row affected, 15 ms execution time.

んっ？？？　エラーにはならないけど。。。
結果1行しかinsertされてない。しかもitem_nameに2行目が入っちゃってます。

postgres=# select * from table_item;
 id_item |       name_item
---------+-----------------------
 111111  | hogehoge \200\r      +
         | 222222  fugafuga \300
(1 行)

データによってはこんなエラーが出るケースも

ERROR:  unterminated CSV quoted field

引用符が悪さしているので、データの中に発生しそうにないバックスペース(x08)を引用符として指定してみた。

copy table_item from 'item.tsv' with csv delimiter e'\t' quote e'\x08' encoding 'sjis';

とりあえず、これでタブ区切りのデータcopyでエラーが出ることはなくなった。
こんな対応で良かったのだろうか。もっとエレガントな解決方法があるかもしれない。

PostgreSQLを使ってみたい、SQLを学んでみたい方へ

　私は1995年頃から業務でSQL書き続けているが、この書籍は入門者はもちろん、ある程度知っている方にもお薦め。最近はWindow関数が充実して便利になったものだ。

10年戦えるデータ分析入門 SQLを武器にデータ活用時代を生き抜く (Informatics &IDEA)