PostgreSQLの「COPY TO」で改行含むデータの出力を行いたい。

Question

携帯から質問しているためマークダウン記法が使えないないです。申し訳ございません。

PostgreSQLの「COPY TO」を使用して改行含むデータを出力したいです。

【テーブル】
COL1, COL2, COL3
0001, a, あいうえお<CRLF(←↓)>かきくけこ

※COL3のデータには改行コード(CRLF)が含まれています。

【実行コマンド】コマンドプロンプトで実行
psql -h localhost -p 5432 -U postgres -d testdb -c "COPY (SELECT * from test) TO STDOUT WITH encoding 'SJIS' CSV HEADER DELIMITER ',' FORCE QUOTE *;" 1> "c:\work\test.csv"

【出力結果】
col1,col2,col3
"0001","a","あいうえお

かきくけこ"

【問題点】
改行コードが含まれているに余計な改行コードが付与されてしまう。
あいうえお<←><←↓>かきくけこ

この問題を解決するには、テーブルデータの改行はLFのみにする。
もしくは改行コードをLFに置換して出力する。
のどちらかしかできないのでしょうか？

【試した事】
・外部ファイルで読み込む。
SQLファイルの文字コードをSJIS、UTF8にしても同じ事象になりました。

Answer 1

stdoutが入れている様ですので\copyを使って直接ファイルに出力してはどうでしょうか

psql -h localhost -p 5432 -U postgres -d testdb -c "\COPY (SELECT * from test) TO c:\work\test.csv WITH encoding 'SJIS' CSV HEADER DELIMITER ',' FORCE QUOTE *;"

Comments

[saki_study111]

\COPYを使い出力すると正しく出力されました。
ただエクスポートのSQLが複雑なため一行で書くのが困難であります。

置換するのが良いのでしょうか？

[amura]

-c""の部分をファイルに書いて　-f　で読み込むのはどうでしょうか　-v　置換　もできると思います。
ーーーー訂正　\copyでは-vは使えませんでした。

[amura]

改行コードを入れるのはSTDOUTですのでCOPYでもファイル名にすれば、同じ様にできると思います。''で括る気がしましたが、DBがリモートでは無いのでローカルディレクトリで行けるハズ
ーー＞訂正　\copyコマンドは　-vが使えませんでした（失礼しました）copyは大丈夫です。

[amura]

"COPY (SELECT * from test) TO 'c:\work\test.csv' WITH encoding 'SJIS' CSV HEADER DELIMITER ',' FORCE QUOTE *;"

[saki_study111]

ありがとうございます。

月曜日に試させてもらいます！

[sazi]

@amuraさん
> 改行コードを入れるのはSTDOUT
上記部分の詳細をご教示頂けないでしょうか。

[amura]

copyのマニュアルで「Microsoft Windowsで稼働するサーバの場合は、サーバ上のファイルへのCOPYの場合にのみ復帰/改行（"\r\n"）を出力します。」と有り、行の改行とは思いましたが、実際にwindowsで行った際に内部改行にも付いていました。

[saki_study111]

今試しましたところ思った通りに動作しました。
ありがとうございます。

STDOUT 標準出力について勉強します。

Answer 2

LFCRLFをCRLFに置換すれば良いかと思います。

update test set col3=replace(col3, chr(10) || chr(13) || chr(10), chr(13) || chr(10))

Comments

[saki_study111]

単純にSELECT文を実行すると正しく〈CRLF〉だけが出力されます。
COPY TOで実行すると〈CRCRLF〉で出力されてしまいます。

SELECTする前にUPDATEして置換するか、SELECT時に置換するしかなさそうですか？

[sazi]

select とcopy toで項目の内容が変わったりするというのは記憶にないですね。
リダイレクトせずにファイルを直接指定するとどうなるでしょうか？

[saki_study111]

selectとCOPY TOでは値が変わるというのは初めてですか…

ファイル直接指定はSQLをファイルにして-fで読み込むって事でよろしいでしょうか？

[saki_study111]

SQLファイルを読み込む方式にしても同じ現象になります。

また、そのSQLファイルの文字コードをSJIS、UTF8のどちらも同じ事象になりました。

[sazi]

標準出力ではなくファイルに出力です

[saki_study111]

\COPYを使い出力すると正しく出力されました。
ただエクスポートのSQLが複雑なため一行で書くのが困難であります。

[sazi]

それは、-fオプションで良いと思いますけど。
後は、selectの部分をストアドにしたらどうでしょう。結構短く出来ると思いますが

[saki_study111]

/COPYでストアドを呼び出すって事でしょうか？

[sazi]

ストアドは面倒なので-fで良いと思います。

[sazi]

ただ、\copyは内部的に標準出力を経由しているだけなので、\copyで上手く行ったなら、改行の問題が標準出力の問題とは考えにくいですが。

Answer 3

改行コードを含むカラムがわかっているのであれば、
改行コードをなにか別の記号に置き換えるなどすればよいのではないかと。

【PostgreSQL-0012】改行コードを取り除く方法 - OSS ERP Compiere Distribution Lab