COPYコマンドによるTEXT取り込みについて

質問

PostgreSQLバージョン　9
OS：RedHat

現在Oracleからの移行でPostgreSQLにてTEXT取り込み処理を作成しています。

OracleではSQLLoaderを使用して取り込んでいましたが、
PostgreSQLではCOPYコマンドを使用すれば取り込みできるところまで調べました。
しかし、行末に区切文字があるためうまく取り込みできません。

【コマンド】
\copy hoge from 'hoge.txt' delimiters '|';

【行末に区切文字がある場合NG】
AAA|BBB|CCC|

ERROR: extra data after last expected column
CONTEXT: COPY hoge, line 1: "AAA|BBB|CCC|"

【行末に区切文字がない場合OK】

AAA|BBB|CCC

SQLLoaderでは行末の区切文字があっても取り込めましたが
COPYコマンドでは取り込めないようです。
回避策はないのでしょうか？
（取引先からのTEXTなので相談はして見ますがレイアウトの変更は難しいかもしれません。）

回避策がない場合、行末の区切文字を削除する方法はありますでしょうか？
行末だけの区切文字を削除する方法が分かりません。
（テキストは固定長でないため、桁数を指定した削除ができません）

行末区切文字削除の方法だけでも良いのでお願いします。

回答No.1

noname#212058 · ベストアンサー

行末には必ず区切り文字が存在するのでしょうか？

案1A：
　　1.　COPY先テーブルの最後に 1列ダミー列を追加し、
　　　　COPY実行。
　　2.　COPY実行後に追加したダミー列を削除。

案1B：
　COPY先テーブルにダミー列を入れるのが嫌なら、
　　1.　COPY専用のテーブル (テーブルの最後にダミー列を
　　　　入れたもの) を別に作って、そこにCOPY実行。
　　2.　COPY専用テーブルからCOPY先テーブルに
　　　　INSERT ～ SELECT ～ でデータを移動。
　　3.　COPY専用テーブルを DROP。

案2：
　問題のファイルがテキストであるならば、テキストエディタの
　正規表現置換を使って行末の | を空白に置換。
　検索文字　|$　置き換え文字 (空白) で行けるはず。

　テキストエディタは何でもよいですが、サクラエディタ等が
　使えます。
　http://sakura-editor.sourceforge.net/download.html

自分なら、データ量にもよりますが、案1B を使いそうな気が
します。(COPYファイル数が多かったりすると案2は面倒なので)

お礼

回答ありがとうございます。

やはりダミー列を入れるなど一手間加えないとダメなようですね。
対応方法を持ち帰って検討させていただきます。

テキストファイルが多いので案２は少し面倒ですね。
案１で検討させていただきます。

回答No.2

root139

sed で行末の区切り文字を取っ払ったものを流し込んでやるのはどうですか？

sed -e 's/[|]$//' hoge.txt | psql -U user_hoge -c "\copy hoge from STDIN delimiters '|';" db_hoge

http://itpro.nikkeibp.co.jp/article/COLUMN/20060227/230879/

参考URL：

http://www.postgresql.jp/document/9.1/html/app-psql.html

お礼

回答ありがとうございます。
正規表現はあまり勉強していませんでしたが行末という正規表現もあるのですね。

これを機にもう少し勉強するようにします。