PostgreSQLのCOPYコマンドのファイルパスを変数で渡す方法について

Question

初めてテラテイルを利用させていただきます、質問方法に不備がありましたら申し訳ございません。

前提・実現したいこと

PowerShell（.ps1ファイル）から PostgreSQL（.sqlファイル）に変数を渡してCOPYコマンドを実行したい。

■ファイル構成
. D:\SQLTEST
. 　├ POWERSHELL
.　 │　└ TEST.ps1
.　 ├ SQL
.　 │　└ TEST.sql
.　 └ CSV
.　　　└ TEST.csv

発生している問題・エラーメッセージ

psql : unrecognized win32 error code: 123psql:D:/SQLTEST/SQL/TEST.sql:20: error: :: Invalid argument

該当のソースコード

■TEST.ps1

$SQLF   = "D:\SQLTEST\SQL\TEST.sql"
$CSVDIR = "D:\SQLTEST\CSV"

(以下はサンプルのため適当です。）
$HOSTNAME = "HOST"
$DBUSER   = "DBUSER"
$PORT     = "99999"
$DBNAME   = "DBNAME"

psql -w -a -h $HOSTNAME -U $DBUSER -p %PORT -d $DBNAME -f $SQLF -v CSVDIR="${CSVDIR}"

■TEST.sql

\copy TEST_TABLE from ':CSVDIR\TEST.CSV' with encoding 'sjis' csv; 

PowerShellからpsqlコマンドでPostgreSQLに接続し、
-v オプションで CSVDIRという変数に "D:\SQLTEST\CSV" を入れてsqlファイルに渡しています。

実際のファイルパスは"D:\SQLTEST\CSV\TEST.csv"のため、
sqlファイルのほうで ':CSVDIR\TEST.CSV' と記載しましたが、上手くいきません。

何か対処法等ございましたら、ご教示いただけますと幸いです。
よろしくおねがいいたします。

補足情報（FW/ツールのバージョンなど）

PostgreSQL 12.4

2021/11/03 追記

あれから色々とご指摘をいただきながら自分で試してみまして、
posgresql \copyのfromに変数(csvのパス)を使いたい
上記を参考に、COPYコマンド自体を変数にセットしてみました。

■TEST.sql

\set filepass :CSVDIR'\TEST.CSV';

-- 変数の中身確認
\echo :filepass 

\set copycmd '\copy TEST_TABLE from ':filepass' with encoding ''sjis'' csv;'

-- \copyコマンド実行
:copycmd

実行結果

-- 変数の中身確認
\echo :filepass

D:\SQLTEST\CSV\TEST.CSV  -- ちゃんとファイルパスがセットされている

-- \copyコマンド実行
:copycmd

psql : psql:D:/SQLTEST/SQL/TEST.sql:42: ERROR: syntax error at or near "'sjis'"

なぜ 'sjis' がsyntax errorになったのかは不明ですが、
filepassのエラーは解消されたのかな？と思っています…。引き続き調べています

Answer 1

こんな感じではどうでしょうか。

\copy TEST_TABLE from :'CSVDIR' || '\TEST.CSV' with encoding 'sjis' csv; 

Comments

[L.J]

ご回答いただきありがとうございます。
試してみたのですが、やはり同じエラーが出てしまいました…。

[sazi]

取り敢えず、SQLファイルを分離して、PowerShellでpsql の-cオプションで直接\copyだけ実行するというのは駄目ですか？

[sazi]

追記を見落としていました。
\set copycmd "\copy TEST_TABLE from " :filepass " with encoding 'sjis' csv;"
としてみてはどうでしょうか。

[sazi]

追記の記述で変数が参照できているなら
\copy TEST_TABLE from :CSVDIR || '\TEST.CSV' with encoding 'sjis' csv;
だったらどうですか。

[L.J]

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

>\set copycmd "\copy TEST_TABLE from " :filepass " with encoding 'sjis' csv;"
>としてみてはどうでしょうか。

こちらですが、
error: invalid command \SQLTEST
となりました。
（なぜ D: が無視されたのかわかりませんでした…）

>追記の記述で変数が参照できているなら
>\copy TEST_TABLE from :CSVDIR || '\TEST.CSV' with encoding 'sjis' csv;

こちらも試してみましたが、
error: :CSVDIR||: No such file or directory
となりました。
:CSVDIR が変数として展開されず文字列として扱われているように見えます…。

いろいろとご教示いただいたのに申し訳ございません…。

[sazi]

:CSVDIR'\TEST.CSV'
ではどうですか
試してはいないので、またエラーになるかもしれませんが

[L.J]

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

>:CSVDIR'\TEST.CSV'
>ではどうですか

error: :CSVDIR||: No such file or directory
となりました。
上記と同じく、:CSVDIR が変数として展開されず文字列として扱われているようです…。

[sazi]

「:CSVDIR'\TEST.CSV'」であれば、「:CSVDIR||」というようにエラーに「||」が含まれるないと思うのですが。エラー時に文字結合を「||]に変換しているとは考えにくいですし。
修正が反映されていないのでは？
修正が正しいのなら、変数が参照されていないという事になりますね。
それから、-cオプションは試されました？

[L.J]

ありがとうございます。
>「:CSVDIR'\TEST.CSV'」であれば、「:CSVDIR||」というようにエラーに「||」が含まれるないと思うのですが。エラー時に文字結合を「||]に変換しているとは考えにくいですし。
修正が反映されていないのでは？

申し訳ございません、こちらエラーコードのコピーが前のものが残っておりまして、正しくは下記になります。
error: :CSVDIR: No such file or directory

-cオプションについてですが、TEST.sqlにはcopyコマンドの他にもいろいろ記述しておりまして、（省略していました）ワンラインでは実行できそうにないためSQLファイルで実行しておりました…。

[sazi]

変数をSQLファイル上で参照できるのは確認できているのですよね。
それなのに取得できないというのは、何か取得できている時と違うんじゃないですか？
それから、COPYだけ-cで実行した後に、残りをSQLファイルで行うというのも駄目なんですね。

[L.J]

sazi様
病院にお世話になる用事があり、返信がとても遅れてしまい、大変申し訳ございませんでした。

あれから色々と試しまして、最終的に以下の記述で上手くいきました。

──────────────────────────
\set filepass :CSVDIR'\TEST.CSV'

\set copycmd '\copy TEST_TABLE from ' :filepass ' with encoding ''sjis'' csv;'

:copycmd
──────────────────────────

色々とご教授いただきとても助かりました。
ありがとうございました。

[sazi]

\のエスケープが問題だったという訳ですね。

Answer 2

PowerShell上では、「\」→「\」としないといけないかと。
正規表現：エスケープが必要な文字一覧 | WWWクリエイターズ

$SQLF   = "D:\SQLTEST\SQL\TEST.sql"
$CSVDIR = "D:\SQLTEST\CSV"

ですが、SQLファイルの中でPowerShellの変数を展開するのは無理がありますので、
psqlの\setメタコマンドで変数に格納し、 \set foo bar などとしたなら、
参照するには SQL文中で :foo などとすることで展開されますので、
こちらを応用するとよろしいかと。
psql
高度な機能
変数
と
SQL差し替え
の項目をご覧ください。

Comments

[L.J]

ご回答いただきありがとうございます。
試しに、手元の環境で ':CSVDIR\TEST.CSV' としてみましたが、同様のエラーが出てしまいました…。

わざわざご回答いただいたのに申し訳ございません。
引き続き調べてみます…。

[退会済みユーザー]

SQLファイルの中でPowerShell変数展開させよう、というのはそもそも無理なのでは。

$SQLF = "D:\SQLTEST\SQL\TEST.sql"
$CSVDIR = "D:\SQLTEST\CSV"
とPowerShell内では表記する必要があります、という点だけ申し述べさせていただきます。

TEST.sql を別途動的生成してみるほうがいいでしょうね。
psql上で実行するだけのSQLファイルだとしたら、
\i メタコマンドで外部ファイルを読み込んで実行することができるので、
psql上の変数に代入させるような内容のSQLを記述しておくという方法が考えられます。
psqlの\setメタコマンドで変数に格納し、 \set foo bar などとしたなら、
参照するには SQL文中で :foo などとすることで展開されますので。
https://www.postgresql.jp/document/13/html/app-psql.html