PostgreSQLのJDBC接続でデフォルトスキーマを指定するには接続URLへcurrentSchemaを追加します。例えばこんな感じです。
jdbc:postgresql://localhost:5432/mydb?currentSchema=app_schema
この設定はSELECT * FROM test; のようにスキーマ名を省略したSQLへ適用されます。public.testのようにスキーマ名を直接書いているSQLは切り替わりません。
既存システムのSQLを修正せず接続先のスキーマだけを変更したい場合に使えます。
PostgreSQLの検証環境をDockerで用意する方法はこちらもどうぞ。
なぜスキーマを一括指定したいのか
PostgreSQLで何も設定せずに下記のSQLを発行すると、デフォルトでpublicスキーマが参照されます。
SELECT * FROM test;これは実質的に以下と同じ動作です。
SELECT * FROM public.test;PostgreSQLの標準的なsearch_pathは通常"$user", publicです。接続ユーザーと同名のスキーマが存在しない一般的な環境では、結果としてpublicが参照されます。
リプレース時によくある要件
実務でのリプレース案件だと、こんな要件が出てきます。
- デフォルトの
publicスキーマではなく専用スキーマ（例：app_schema）を用意したい。 - テーブルやビューの構成は既存と全く同じにする。
- 既存のSQL文は変更せずにそのまま流用したい。
SQLファイルが数百・数千あるケースで手動スキーマ名追記は現実的ではありませんし、修正漏れのリスクも高いですよね。
そこで使えるのがJDBC接続時のスキーマ指定です。
JDBC接続文字列で解決する方法
currentSchemaパラメータとは
PostgreSQL公式のJDBCドライバーにはcurrentSchemaというパラメータが用意されています。
currentSchema (String) Default null Specify the schema (or several schema separated by commas) to be set in the search-path. This schema will be used to resolve unqualified object names used in statements over this connection.
Initializing the Driver | pgJDBC
要約すると「指定したスキーマが、スキーマ名を省略したオブジェクト名の解決に使われる」ということです。
接続時にcurrentSchema=app_schemaと指定すれば、アプリ内のすべてのSQL文でスキーマ名が省略されている場合に自動的にapp_schemaを参照してくれます。指定がない場合はPostgreSQLのデフォルトであるpublicが使われます。
適用されるケース・されないケース
公式ドキュメントにある非修飾オブジェクト名の解決という点が重要です。SQL文でスキーマ名をハードコーディングしている場合はcurrentSchemaの指定に関係なく、そちらが優先されます。
| ケース | 説明 | 適用 |
|---|---|---|
| スキーマ名なしのSQL | SELECT * FROM test; | ✅ 適用される |
| スキーマ名ありのSQL（public） | SELECT * FROM public.test; | ❌ 適用されない |
| スキーマ名ありのSQL（別スキーマ） | SELECT * FROM app_schema.test; | ❌ 適用されない |
設定方法（jdbc.diconの例）
S2JTAやSeasar2を利用している場合jdbc.diconなどの接続設定ファイルを以下のように変更します。
jdbc:postgresql://localhost:5432/mydbjdbc:postgresql://localhost:5432/mydb?currentSchema=app_schemaたったこれだけです。
動作イメージ
設定後にアプリケーションから下記のSQLを発行すると、
SELECT * FROM test;PostgreSQL内部では以下のように解釈されます。
SELECT * FROM app_schema.test;既存のSQL文を一切変更せずにスキーマ切り替えが完了します。
currentSchemaとsearch_pathの違い
currentSchemaはJDBC接続向けの設定です。psqlで一時的にスキーマを切り替えたい場合や接続ユーザーごとに同じ設定を継続して使いたい場合はSET search_pathやALTER ROLEを使います。
どの方法もスキーマ名を省略したテーブルやビューを検索する順番を指定するものです。設定が適用される範囲とタイミングが異なります。
| 方法 | 適用範囲 | 設定例 | 向いているケース |
|---|---|---|---|
JDBCのcurrentSchema | JDBC接続ごと | ?currentSchema=app_schema | アプリケーション単位で切り替えたい |
SET search_path | 現在のセッション | SET search_path TO app_schema, public; | psqlでの一時確認や処理単位で切り替えたい |
ALTER ROLE ... SET | 対象ユーザーの新しい接続 | ALTER ROLE app_user SET search_path TO app_schema, public; | ユーザー単位で継続して適用したい |
ALTER DATABASE ... SET | 対象データベースへの新しい接続 | ALTER DATABASE mydb SET search_path TO app_schema, public; | データベース単位で継続して適用したい |
JDBC接続だけ切り替えるならcurrentSchema
JavaやSpring BootなどJDBCを使うアプリケーションだけ参照先を変えたい場合はcurrentSchemaが分かりやすいです。
jdbc:postgresql://localhost:5432/mydb?currentSchema=app_schemaこの設定はJDBC接続を作成するときに検索パスを指定します。PostgreSQL全体やpsqlから作成した別の接続には影響しません。
アプリケーションごとに接続URLを分けられるため、既存SQLを変更せず特定のアプリだけ参照スキーマを切り替えたい場合に向いています。
psqlで一時的に切り替えるならSET search_path
psqlで接続した後一時的に参照スキーマを変更する場合はSET search_pathを実行します。
SET search_path TO app_schema, public;この設定はSQLを実行した現在のセッションにだけ適用されます。psqlを終了して接続し直すと通常は元の設定へ戻ります。
設定結果は次のSQLで確認できます。
SHOW search_path;
SELECT current_schema();SHOW search_pathでは検索対象となるスキーマの順番、current_schema()では現在最初に参照される有効なスキーマを確認できます。一時的な動作確認や管理作業中だけスキーマを切り替えたい場合はこの方法が手軽ですね。
ユーザー単位で継続するならALTER ROLE
特定のデータベースユーザーが接続したとき、毎回同じ検索パスを使いたい場合はALTER ROLEで設定します。
ALTER ROLE app_user
SET search_path TO app_schema, public;設定後app_userが新しく接続するとapp_schema、publicの順でオブジェクトを検索します。
すでに接続中のセッションには反映されないため、設定後に再接続してください。接続プールを使っている場合は既存の接続が残らないようにアプリケーションや接続プールの再起動も必要です。
同じユーザーを複数のアプリケーションで共有している場合、すべてに設定が影響します。特定のアプリケーションだけ切り替えたい場合はALTER ROLEよりcurrentSchemaの方が安全です。
データベース単位で継続するならALTER DATABASE
対象データベースへ接続するユーザーに共通の検索パスを設定したい場合はALTER DATABASEを使います。
ALTER DATABASE mydb
SET search_path TO app_schema, public;この設定も新しく作成された接続から適用されます。データベースを利用する複数のユーザーへ影響するため、アプリケーションごとに参照先を分けたい環境には向きません。
アプリとユーザーの設定が重なる場合
ALTER ROLEとALTER DATABASEの両方が設定されている環境では、ユーザーとデータベースの組み合わせによって適用される設定が変わることがあります。
運用中の環境では変更前に次のSQLで現在の検索パスを確認してください。
SHOW search_path;JDBC接続だけを変更したい場合はcurrentSchema、psqlで一時的に変更したい場合はSET search_path、ユーザーやデータベースの標準設定として残したい場合はALTER ROLEまたはALTER DATABASEと使い分けると分かりやすいです。
複数スキーマを検索パスに含める場合
currentSchemaパラメータはカンマ区切りで複数のスキーマを指定できます。
jdbc:postgresql://localhost:5432/mydb?currentSchema=app_schema,publicこの設定ではまずapp_schemaを検索し、見つからなければpublicスキーマを参照します。公式のサンプルデータや拡張機能などをpublicに残しておきたい場合に便利ですね。
注意点とトラブルシューティング
スキーマ名の大文字・小文字について
大文字を含む引用符付きスキーマ名は接続URL上の指定方法やURLエンコードで詰まりやすいため、原則として小文字のスキーマ名を推奨です。既存環境で大文字を使っている場合はSHOW search_pathとcurrent_schema()で接続後の値を確認してください。
接続プールを使用している場合
接続プールを利用している環境では設定変更後にアプリケーションサーバーの再起動が必要です。既存の接続が残っていると古い設定が使われ続けることがあるので注意してください。
権限エラーが出る場合
下記のようなエラーが出るケースがあります。
ERROR: permission denied for schema app_schema接続ユーザーに対してスキーマのUSAGE権限が付与されているか確認してください。
GRANT USAGE ON SCHEMA app_schema TO 【DBユーザー名】;テーブルへのアクセス権限も別途必要です。
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app_schema TO 【DBユーザー名】;よくある質問
PostgreSQLやDocker関連記事はここでまとめて確認！
まとめ
- JDBC接続文字列に
currentSchemaパラメータを追加するだけで全SQLでスキーマが自動補完されるよ。 - スキーマ名を省略していないSQL（ハードコーディングあり）には適用されないので注意だよ。
- カンマ区切りで複数スキーマの検索パスを設定できるよ。
- 接続ユーザーへのスキーマ
USAGE権限とテーブルアクセス権限を忘れずに付与してね。 - 接続プール環境では設定変更後にアプリサーバーの再起動が必要だよ。
リプレースや環境移行の作業効率が大幅に上がりますので、ぜひ活用してみてくださいね。