Q&A
> COPY TO コマンドを使用してSQLファイルを読み込むと改行、コメント行がある場合に正しく実行されません。
その具体的な内容を質問に追記して下さい。
「コメント行がある場合に」とはSQLファイル中にあるコメントという事でしょうか？
【実施したい事】
windowsのbatを実行→PostgreSQLに接続→SQLファイルを読込みCSVファイルを出力
【問題点】
以下のコマンドを使用してSQLファイル(改行、コメント行含む)を
読み込む場合に正しく実行されません。
ログファイルの日本語部分が文字化けするのですがどこの箇所で指定すればいいのでしょうか？
解決策、別の方法があれば教えて頂きたいです。よろしくお願いします。
※SQLはサンプル用に単純なものにしてあります。
[batファイル]
@echo off
rem ●ログファイルに日時を書き込む
echo ■DATETIME: %DATE% %TIME% 2>&1> "c:\work\log\test.log"
rem ●エクスポート
(psql -h localhost -p 5432 -U postgres -d mydb -f "c:\work\sql\sample.sql" 2>&1)>> "c:\work\log\test.log"
[SQLファイル]
¥COPY(
select
col1,col2,col3
from
test1 -- test1
where
1=1
) TO 'C:¥work¥output.csv' WITH csv DELIMITER ',';
[環境]
OS：windows10
PostgreSQL：11
質問が編集されましたが、肝心のCOPY TOをどのように使用されているかは書かれていません。
質問の内容は単にselectの結果を出力しているように見えますが。
copy toも色々試していたため
ごっちゃになっていました。
とりあえずCOPY TOの部分は消しました。
同様なケースを実行してみましたが（複数改行コード）特に問題がなさそうです。「正しく実行されません」とはどの様な状態でしょうか？
SQLファイルの部分に誤りがあったので修正しました。
よろしくお願いします。
当初と解決したい内容が変わっていませんか？
CSV出力は上手くいって、ログファイルの問題だけ残っているという事でしょうか？
CSV出力に関してもまだ失敗しています。
¥COPY:行の末尾で構文解析エラーと出ております。
ログファイルは当初から日本語が文字化けしていてその課題も残っております。
\copyはpsqlのラインコマンドなので改行した時点で実行されエラーとなります。￥の付かないcopyコマンドをお使いください。
ローカルの端末から別のDBサーバに繋ぎにいく際には¥COPYでないといけないと調べたのですが…
※SQLはDBサーバで実行と書いていなかったかもしれません…すいません。
その通りです。copyだと実行がサーバ側で行われるので、サーバ側に設定する事になります。Linux上ではstdoutを使えるのですが、windowsは違う様でエラーになりますね？？ localhostならと期待してました
確かにサーバー側で
¥なしCOPYではCSVが出力されることが確認できました。
ただ各端末から実行したい場合は改行含むことはどうしても出来ないのでしょうか？
SQLを1行で書かないといけない。
SQLコメントをブロックコメントで書かないといけない等がありますので。
copyにして
TO stdout WITH csv DELIMITER ',';に変更して
-f "c:\work\sql\sample4.sql" >"C:\work\output.csv")で作成できます。
ご参考になりそうなサイトをみつけました。よろしければhttps://qiita.com/rzl5/items/e0863d20e7c96e8b49a7
COPY -f "SQLファイル">"CSVファイル" TO stdout WITH csv DELIMITER ',';
ということでしょうか？
教えて頂いたURLの通りに実行すればローカルからサーバーを見ることができました！ サーバー間通信を行うならSTDOUTが重要だったみたいですね。
お聞きしたいのが
PSQL_COMMANDは具体的に何をしているのでしょうか？
体験的にお答えしています。psqlは基本的にSQL文を送信し、返信データを表示する、クライアントTEMINALと同じようなのもと思われます。teratermのマクロみたい？にバックスラッシュコマンドを持っていて「\i ファイル」「\o」とかを持っております。変数も定義できます。
copyコマンドの場合ファイル名をサーバ込みでサーバに送るのてサーバ側のディレクトリが対象になります。\copyはローカルファイルを読んで中のデータを送信し、実行結果を受信していると思われます。
私どものシステムでも通信軽減にためにcopyで実行してサーバよりftp getする場合が有ります。
ターミナルでSQLを実行するのと同じような感じなのですね。
詳しく教えていただきありがとうございます。
なるほど、その方が的を得ている。
回答3件
0
SQL
1COPY ( 2 クエリー 3) TO 'フルパスのファイル名' 4WITH (FORMAT CSV, HEADER True, DELIMITERDELIMITER ',', QUOTE '"')
指定するSQLファイルの内容は、上記のような内容になるでしょうね
オプションの詳細はリファレンスにありますので、確認されて下さい。
COPY
出力するファイル名などをSQLファイルに渡したい場合には以下が参考になるでしょう。
PostgreSQLシェルスクリプトでSQLに引数を渡して実行
投稿2020/01/26 14:48
編集2020/01/27 00:20総合スコア25428
\COPYコマンドはローカルファイルを橋渡ししてくれますが、COPYコマンドではDBサーバーから見える範囲のパスである必要があります。
手っ取り早くは、共有ディレクトリを作成して、そこをファイルのやり取りの場所にする事です。
尚、COPYコマンド内で指定するパスはサーバー側から見たパスである必要があります。
0
自己解決
教えていただいたことをまとめました。
【windows.bat側】
@echo off
cd /d %~dp0
set PGHOST=ホスト名
set PGDATABASE=DB名
set PGUSER=スキーマ名
set PGPASSWORD=パスワード
set PSQL_COMMAND="C:\Program Files\PostgreSQL\11\bin\psql.exe" ←PostgreSQLをインストールした先のexe
%PSQL_COMMAND% -f "実行するSQLパス" > "出力するCSVパス"
【SQL側】
COPY (
select
col1, col2, col3
from
test -- test
where
1=1
) TO STDOUT WITH CSV DELIMITER ',';
※外部のSQLサーバに繋ぐ場合は\COPYだが「STDOUT」を使用することにより外部SQLサーバに接続でき、
「\COPY」から「COPY」に変更できる。
「\COPY」ではSQL文を1行で書かないといけないが、「COPY」では改行もいける。
【補足】
windows.batでerrorlevelを取得しようとする際に「%PSQL_COMMAND%」コマンド的には絶対に成功するため
戻り値が0になる。そのため出来上がったCSVファイルサイズが0kbでない場合は成功とするべきなのかな・・・
DBサーバのpostgresql.confで「lc_messages = 'ja_JP.UTF-8'」に変更してもSQLエラーメッセージは文字化けのまま・・・
投稿2020/01/27 23:52
総合スコア13
標準出力経緯で文字化けしているなら、コンソールのコードセットがutf-8じゃないのでは？
どこの設定のことなのかがよく分かっていません。
postgresql.confのlc_messageはja_JP.UTF-8で設定しています。
SQL ShellでClient EncodingをUTF8に設定した場合にエラーメッセージが文字化けします。
SJISを指定すると日本語のエラーが表示されます。
（存在しないテーブルのselectを流しました）
※バッチから実行するとSJISでも化けてしまいます。
0
psqlでCOPYコマンド を実行することでexport/importできます。
投稿2020/01/26 01:31
総合スコア16417
COPYコマンドを使い実行する事はできるのは確認できているのですが、SQLを直接書くのではなくSQLファイルを読み込ませて実行する方法が知りたいのです。
oracleで言えばSQLファイルの中にspoolを書くイメージで方法を模索しております。
あなたの回答
tips
プレビュー
