PostgreSQLのERROR:推定最終列の後に余計なデータがありましたへの対処方法

Question

件名の通りです。対処方法をご教授いただけませんでしょうか？よろしくお願いいたします。

解決したい問題
下記エラーの対処方法をご教授いただきたいです。

一応の解決方法
下記で一応動きました。。

\encoding utf8

drop table access_log;
\i src/access_log.ct
\copy access_log from 'src/access_log.bz2.csv' with csv header null '' delimiter ',' quote e'"';

エラー内容

2018-11-30 08:55:31 JST ERROR:  推定最終列の後に余計なデータがありました
2018-11-30 08:55:31 JST コンテキスト:  access_logのCOPY。行番号 18677590: "hoge.fuga.net.mx~-~-~23/Sep/2018:10:16:30 +0900~GET~/index.php?q=hoge_fuga&search_..."
2018-11-30 08:55:31 JST ステートメント:  COPY  access_log FROM STDIN WITH CSV DELIMITER E'~' header null '';

TSVファイルの検索結果

$ grep -n hoge.fuga.net.mx access_log.tsv
16632811:hoge.fuga.net.mx~-~-~23/Sep/2018:10:16:30 +0900~GET~/index.php?q=hoge_fuga&search_id=1234"~200~144241~-~-
16632832:hoge.fuga.net.mx~-~-~23/Sep/2018:10:16:32 +0900~GET~/index.php?q=hoge_fuga&search_id=1234&page=piyo~200~331344~-~-

$ tail -10 access_log.tsv
m123.piyo.piyo.ne.jp~-~-~01/Oct/2018:01:59:58 +0900~GET~/index.php?&action=ghi&q%3Dabc%26id%3D123&text=&_=123~200~38~https://www.fuga.com/index.php?q=abc&id=1234~Mozilla/5.0 (iPhone; CPU iPhone OS 11_4_1 like Mac OS X) AppleWebKit/605.1.15 (KHTML, like Gecko) Mobile/15G77 YJApp-IOS jp.co.yahoo.ipn.appli/4.11.6
m123.piyo.piyo.ne.jp~-~-~01/Oct/2018:01:59:58 +0900~GET~/index.php?&action=def~200~56~https://www.fuga.com/~Mozilla/5.0 (iPhone; CPU iPhone OS 11_4_1 like Mac OS X) AppleWebKit/605.1.15 (KHTML, like Gecko) Mobile/15G77 YJApp-IOS jp.co.yahoo.ipn.appli/4.11.6
m123.piyo.piyo.ne.jp~-~-~01/Oct/2018:01:59:58 +0900~GET~/index.php?&q=abc&id=123~200~306~https://www.fuga.com/~Mozilla/5.0 (iPhone; CPU iPhone OS 11_4_1 like Mac OS X) AppleWebKit/605.1.15 (KHTML, like Gecko) Mobile/15G77 YJApp-IOS jp.co.yahoo.ipn.appli/4.11.6
1-2-3.hoge.fuga.ad.jp~-~-~01/Oct/2018:01:59:58 +0900~GET~/index.php?q=123&search=%AB%BC%DF~200~44979~https://www.google.com/~Mozilla/5.0 (Linux; Android 8.1.0; F-04K Build/V18R067C) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/69.0.3497.100 Mobile Safari/537.36
123.hoge.fuga.ne.jp~-~-~01/Oct/2018:01:59:59 +0900~GET~/index.php?&q=def&search=~200~51~https://www.hoge.net/index.php?q=123~Mozilla/5.0 (iPhone; CPU iPhone OS 12_0 like Mac OS X) AppleWebKit/605.1.15 (KHTML, like Gecko) Mobile/16A366 YJApp-IOS jp.co.yahoo.ipn.appli/4.11.6
123.hoge.fuga.ne.jp~-~-~01/Oct/2018:01:59:59 +0900~GET~/index.php?&q=def~200~56~https://www.hoge.net/~Mozilla/5.0 (iPhone; CPU iPhone OS 12_0 like Mac OS X) AppleWebKit/605.1.15 (KHTML, like Gecko) Mobile/16A366 YJApp-IOS jp.co.yahoo.ipn.appli/4.11.6
Oct  1 02:00:00 svr1 newsyslog[50902]: logfile turned over
hgoe.fuga.co.jp~-~-~01/Oct/2018:02:00:00 +0900~GET~/blog/?q=1~200~19843~-~-
hoge1234567890.fuga.net~-~-~01/Oct/2018:02:00:00 +0900~GET~/index.php?q=abc&page_id=1234&gclid=ABCDEFG~200~47956~-~Mozilla/5.0 (iPhone; CPU iPhone OS 10_2_1 like Mac OS X) AppleWebKit/602.1.50 (KHTML, like Gecko) GSA/59.0.213668279 Mobile/14D27 Safari/602.1
.

バッチファイル

call .\parameters-windows.bat
%PSQL% -f load.sql
pause

rem parameters-windows.bat
set PGHOST=localhost
set PGPORT=5432
set PGDATABASE=postgres
set PGUSER=postgres
set PSQL="C:\Program Files\PostgreSQL\9.4\bin\psql"

SQL

\encoding utf8

drop table access_log;
\i src/access_log.ct
\copy access_log from 'src/access_log.bz2.tsv' WITH CSV DELIMITER E'\t' header null '';

create table access_log
( a text
, b text
, c text
, d text
, e text
, f text
, g text
, h text
, i text
, j text
);

環境

• Windows10
• PostgresSQL 9.4

参考サイト
PostgreSQLのcopyではまった

Answer 1

推定最終列の後に余計なデータがありましたは項目に区切り記号を含むデータの場合に発生します。
区切り記号である~が含まれているのでしょう。

COPYコマンドのオプションで文字列の区切り記号をQUOTEで指定できます。
CSV作成時に項目を"などで括るようにすれば、項目の内容まで区切り記号を検索することは無くなりますし、通常の,区切りのCSVでの対応も可能になります。

追記

行数を見るとかなり大きなものになっています。
CSVを分割して作成した方が、COPYでのトランザクション分割となってTOTALでは処理時間は短縮されるでしょうし（並列実行も可能になる）、ファイル内容の確認も可能になりますので、検討されてはどうでしょうか。

Comments

[退会済みユーザー]

saziさん、ご回答ありがとうございます。一応replaceで~を削除しているつもりなのですが、まだ残っているんですね。。
COPYコマンドのオプションのご教示、分割作成のご提案、ありがとうございます。検討してみます。

Answer 2

カンだけど、16632811行目の「"」はいいのか？
「\copy」コマンドだから念のため
psqlクライアントアプリケーションをチェックしたものの、
気になるとしたらあとはCSVとかTSVとか作ったときの盲点として、
最後データの終了を示す「.」のみの行がないことくらいかなぁ。

私のなんてたいした回答ではないけど
https://teratail.com/questions/161219
とか起こされたら嫌気が差すわ。

Comments

[退会済みユーザー]

m6uさん、ご回答ありがとうございます。
下記は読み込めています。。
$ head -772856 access_log.tsv | tail -1
hoge.fuga.com~-~-~02/Sep/2018:07:46:46 +0900~GET~/index.php?q=abc&q_id=1234"~200~143699~-~-

[退会済みユーザー]

tsvファイルには「.」はあるのかな？

[退会済みユーザー]

tsvファイルには「.」は無いです。
最後行に「.」のみの行を追加してみます。

[退会済みユーザー]

tsvファイルに「.」を書いたらどうなるかな？

[退会済みユーザー]

tsvファイルの最終行に「.」を追加しましたが、同じエラーが発生しました。

[退会済みユーザー]

m6uさん、気分を害してしまい申し訳ございませんm(__)m
違う内容になったかと思い、別の質問を作成していまいました。
たいした回答ではないとは思っておりません。