スキーマとは
- 1つのDBの中に複数設定することができる名前空間のこと
- 階層化できないディレクトリ構造のようなものという認識でok
- テーブルを含めた、全てのオブジェクトはいづれかのスキーマに必ず所属する（ディレクトリ構造と同じ）
- スキーマが異なれば、同じ名前のテーブルを作成してもいい（ディレクトリ構造と同じ）
- スキーマを省略してテーブルなどのオブジェクトを参照することができる（一般的に省略する）
- 省略した場合は、スキーマ検索パスに指定されているスキーマ内からオブジェクトを参照する
- スキーマ検索パスは変更可能（postgresql.conf）
- スキーマを作成、削除することもできる
- PostgreSQLでDBを作成すると6つのスキーマが作成される（以下では、主なスキーマを表示）
| スキーマ | 説明 |
|---|---|
| information_schema(情報スキーマ) | データベースのメタデータを取得するために利用されるビューの集まり。標準SQL（PostgreSQL独自のSQLでない）で定義されており、MySQLやOracleなどの他のDBでも存在している。 |
| pg_catalog(システムカタログ) | PostgreSQLがシステム的に備えているオブジェクトを格納しているスキーマ。pg_catalogの中に存在するオブジェクトは基本的にpg_という名前で始まる。 |
| public | テーブルやインデックスなどを新規作成する場合は、基本このスキーマの下に作成される。 |
スキーマ検索パス
- スキーマ名を省略して、オブジェクトを参照する場合に使用される（一般的）
- スキーマ検索パスはpostgresql.conf内の
search_pathで設定されている - デフォルトでは、
"$user"と"public"でユーザー名と同名のスキーマとpublicのスキーマが使用できるようになっている - スキーマの優先順位として、左のスキーマから順に検索される
- まず、$userスキーマが参照されそこでオブジェクトがなかった場合に、publicスキーマを参照する
- #でコメントアウトされていてもデフォルトの設定として有効になっているとのこと
postgres=# SHOW search_path;
#search_path = '"$user",public' # schema names
※ "$user"はデータベースユーザーのスキーマ
スキーマ検索パスの変更
-- スキーマ検索パスの変更
postgres=# SET search_path TO schemaA,schemaB,schemaC,public;
スキーマを省略しない場合(SELECT時)
書式①：SELECT * FROM DB名.スキーマ名.テーブル名;
書式②：SELECT * FROM スキーマ名.テーブル名;
- これだと冗長
- 通常、DBに接続してコマンド（SELECT）を実行するためDB名は省略可
スキーマを省略する場合(SELECT時)
書式：SELECT * FROM テーブル名;
- この方法が一般的に使用される
- スキーマ検索パスを意識する必要がある
- postgresql.confの
search_pathに指定されているスキーマを左から優先して参照する - 実際スキーマが省略された場合でも、以下のように参照が行われている
-
SELECT * FROM $user.テーブル;→SELECT * FROM public.テーブル; - 参照したスキーマにSELECTしたいオブジェクトがない場合はエラー
- postgresql.confの
作成済みのスキーマを確認する
- 以下では、postgres（DB）内に存在するスキーマの一覧を表示する
- また、スキーマの所有者も確認することができる
postgres=# \dn
スキーマを作成、削除
- 接続しているDB内にスキーマを作成、または削除する
- 以下では、postgres（DB）内にスキーマを作成する
- スキーマを作成するには、スーパーユーザーか、スキーマを作成するデータベースでCREATE権限を持っている必要があります。
スキーマの作成
-- "test_schema" というスキーマを作成
postgres=# CREATE SCHEMA test_schema;
-- スキーマ名は省略可能。ユーザー名がスキーマ名となる
postgres=# CREATE SCHEMA userB;
- 以下では、postgres（DB）内のtest_schema（スキーマ）を作成する
スキーマの削除
-- "CASCADE"は、スキーマ削除時にオブジェクトも合わせて削除するオプション
postgres=# DROP SCHEMA test_schema CASCADE;
スキーマの変更
ALTER SCHEMA スキーマ名 RENAME TO 新しいスキーマ名;
ALTER SCHEMA スキーマ名 OWNER TO 新しい所有者名;
- 変更できること
- スキーマ名
- スキーマの所有者
スキーマの変更
-- "test_schema" というスキーマ名を変更
postgres=# ALTER SCHEMA test_schema RENAME TO new_schema;
-- "new_schema"の所有者を変更
postgres=# ALTER SCHEMA new_schema OWNER TO user1;
スキーマの所有者
- スキーマを作成したロールがスキーマの所有者になる
-
AUTHORIZATIONオプションを指定することでコマンド実行時にスキーマの所有者を指定することができる - スキーマの所有者を別のロールに変更することも可能
- スキーマの所有者だけが、スキーマ内にオブジェクトを作ったり、スキーマ内のオブジェクトを参照できる
- 他のロールがスキーマにアクセスできるようにするには、GRANT文で適切に権限を付与する必要がある
-- "schemaB" というスキーマを作成し、スキーマの所有者は以下のコマンドを実行したロール
postgres=# CREATE SCHEMA schemaB;
-- "schemaA" というスキーマを作成し、スキーマの所有者は "userA"
postgres=# CREATE SCHEMA schemaA AUTHORIZATION userA;