Help us understand the problem. What is going on with this article?

【PostgreSQL】psqlのCOPYコマンドに変数で絶対パスを渡す

はじめに

サーバーリプレース作業にて、データベースを Oracle 11g から PostgreSQL 9.6 に移行作業をしています。
Oracle 用に作成していたバッチを、PostgreSQL 用にしていく作業で同僚が嵌っていたので、休日を利用して調査してみました。

嵌りポイント

pg_dump コマンドではスキーマやtオプションでテーブル単位でダンプファイルが作成できます。
しかし、条件を指定して一部のテーブルデータのダンプファイルは作成できません。

ネットで軽く調べてみると COPY コマンドを使用すればいいことが分かりました。

COPY  (SELECT * FROM sample_table WHERE id = '123') TO 'sample_dump';

これで出来そうなので同僚に引き渡し他作業をしていたところ、何やら悩んでいるようでした。聞いてみるとダンプファイルが作成できないとのこと。

絶対パス問題

TOに指定できるのは絶対パスのみで相対パスだと「relative path not allowed for COPY to file」エラーとなってしまうのです。
バッチを実行した下位フォルダ配下にダンプファイルを作成したいので絶対パスにはしたくないわけですよね。
また作りとして、バッチファイルとSQLファイルを分けて作成したいのです。

※相対パスはpsql起動時のカレントディレクトリが基点なら指定できるようです。

対応

試行錯誤した結果、psqlのvオプション(変数)を使用して絶対パスを渡すことで解決しました。
変数はSQLのパラメータ化クエリのプレースホルダのように条件にしか使用できないという思い込みがあったのですが、どこでも使用できます。

Sample.bat
@ECHO OFF
cd /d %~dp0

REM PostgreSQLのインストールパスのbinディレクトリ
set PGPATH=C:\PostgreSQL\9.6\bin\
set PGPASSWORD=postgres

set curPath=%~dp0

%PGPATH%psql -h localhost -U postgres -d postgres -f "Sample.sql" -v value='123' -v inputpath="'%curPath%dump/sample_dump'"
Sample.sql
SET CLIENT_ENCODING TO 'UTF8';
COPY (SELECT * FROM sample_table WHERE id = :value) TO :inputpath;

関連調査

この件を調べている際に他にも分かったことがあったのでメモ用に記載しておきます。

COPY コマンドと \COPY コマンドの違い

PostgreSQL は COPY コマンドと \COPY コマンドの2種類がありました。

COPY コマンド

PostgreSQL データベースが稼働しているサーバー上のファイルの入出力を行うためのものです。

  • 権限:スーパーユーザーしか実行できません。
  • ファイル作成場所:サーバ側/絶対パスかpsqlを起動した際のパスからの相対パスで指定します。
  • ファイル属性:サーバーを起動しているOSユーザの所有
alter role (ユーザー名) SUPERUSER;

\COPY コマンド

データベース接続を行ったクライアント側でファイルの入出力を行うものです。

  • 権限:一般ユーザでも実行できる。
  • ファイル作成場所:クライアント側/絶対パスまたは相対パス
  • ファイル属性:psqlを起動したOSユーザの所有

変数の指定方法

シングルクォーテーションはSQL側で :'value' のようにも指定できます。

Sample.bat
%PGPATH%psql -h localhost -U postgres -d postgres -f "Sample.sql" -v value=123 -v inputpath="'%curPath%dump/sample_dump'"
Sample.sql
SET CLIENT_ENCODING TO 'UTF8';
COPY (SELECT * FROM sample_table WHERE id = :'value') TO :inputpath;

IN句の指定する際に一つの変数 value='123','125' で出来ます。

Sample.bat
%PGPATH%psql -h localhost -U postgres -d postgres -f "Sample.sql" -v value='123','125' -v inputpath="'%curPath%dump/sample_dump'"
Sample.sql
SET CLIENT_ENCODING TO 'UTF8';
COPY (SELECT * FROM sample_table WHERE id IN(:value)) TO :inputpath;

変数はsample_:nameのように末尾なら結合して使用できます。
PostgreSQL :: psql で 変数に値を渡す

Sample.bat
%PGPATH%psql -h localhost -U postgres -d postgres -f "Sample.sql" -v name=table -v value=123 -v inputpath="'%curPath%dump/sample_dump'"
Sample.sql
SET CLIENT_ENCODING TO 'UTF8';
COPY (SELECT * FROM sample_:name WHERE id = :'value') TO :inputpath;

相対パスの指定方法

\COPY コマンドを使用すれば相対パスで指定できます。
サーバー上で動作させるならこの指定方法であっても動作します。

ただ残念ながら、この方法だと変数を指定することができません。

Sample.sql
SET CLIENT_ENCODING TO 'UTF8';
\COPY (SELECT * FROM sample_table WHERE id = '123') TO 'dump/test.dump';

Bashなら下記の方法で変数を使う案もあるようです。
posgresql \copyのfromに変数(csvのパス)を使いたい

\set copycmd '\\copy hoge from ':inputfile' (format csv, header true);'
:copycmd

最後に

SQLを使用する際には速度向上やインジェクション対策用にパラメータ化クエリとしてプレースホルダを使用しているので、そんなことには使えないんじゃないかという思い込みで試さなかったりします。なんで、ちゃんと調べないと駄目ですね。

psqlのオプションは大文字と小文字で機能が違いますので注意が必要です。
COPY コマンドと \COPY コマンドとか機能が違うってややこしいわい。

yaju
静岡県島田市在住のシニアSEがコンピューター、機械学習、Unity、数学について考える
http://yaju3d.hatenablog.jp/
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした