【PostgreSQL】スキーマ一覧を確認する

\dnコマンドでスキーマ一覧を確認する

PostgreSQLでスキーマ一覧を確認するには、psqlコマンドラインツールの\dnコマンドを使う。

\dn
      List of schemas
  Name  |       Owner
--------+-------------------
 app    | postgres
 public | pg_database_owner
 report | app_user
 sales  | postgres
(4 rows)

pg_catalogやinformation_schemaなどのシステムスキーマは除外され、自分で作成したスキーマとpublicスキーマだけが表示される。

PostgreSQL 15以降で新しく作成したクラスタやデータベースでは、publicスキーマの所有者がpg_database_ownerになる。
pg_upgradeやダンプのリストアで移行した環境では以前の所有者を引き継ぐため、postgresのままの場合もある。

\dn+で権限とコメントも表示する

+を付けるとアクセス権限とコメントの列が追加される。

\dn+
                                       List of schemas
  Name  |       Owner       |           Access privileges            |      Description
--------+-------------------+----------------------------------------+------------------------
 app    | postgres          |                                        |
 public | pg_database_owner | pg_database_owner=UC/pg_database_owner+| standard public schema
        |                   | =U/pg_database_owner                   |
 report | app_user          |                                        |
 sales  | postgres          |                                        |
(4 rows)

Access privileges列はロール名=権限記号/権限を付与したロール名の形式で表示される。
スキーマに対する権限記号はUがUSAGE、CがCREATEを表す。
ロール名が省略された=U/pg_database_ownerはPUBLIC（全ロール）への付与を意味する。
PostgreSQL 14以前はPUBLICにCREATE権限も付与されていたため=UC/postgresと表示される。

Access privileges列が空欄のスキーマは、権限を追加で付与していないデフォルトの状態である。
所有者とスーパーユーザーだけがアクセスできる。

\dnSでシステムスキーマも表示する

Sを付けるとシステムスキーマも含めて表示する。

\dnS
            List of schemas
        Name        |       Owner
--------------------+-------------------
 app                | postgres
 information_schema | postgres
 pg_catalog         | postgres
 pg_toast           | postgres
 public             | pg_database_owner
 report             | app_user
 sales              | postgres
(7 rows)

名前で絞り込む

\dnの後ろにパターンを指定すると、名前が一致するスキーマだけを表示する。

\dn app*
 List of schemas
 Name |  Owner
------+----------
 app  | postgres
(1 row)

パターンを指定した場合はSを付けなくてもシステムスキーマが検索対象に含まれる。

\dn pg*
    List of schemas
    Name    |  Owner
------------+----------
 pg_catalog | postgres
 pg_toast   | postgres
(2 rows)

information_schema.schemataでスキーマ一覧を確認する

SQLでスキーマ一覧を取得する場合はinformation_schema.schemataビューを参照する。

SELECT schema_name, schema_owner
FROM information_schema.schemata
ORDER BY schema_name;

    schema_name     |   schema_owner
--------------------+-------------------
 app                | postgres
 information_schema | postgres
 pg_catalog         | postgres
 pg_toast           | postgres
 public             | pg_database_owner
 report             | app_user
 sales              | postgres
(7 rows)

\dnと違いシステムスキーマも含まれるため、自分で作成したスキーマだけに絞る場合はpg_から始まるスキーマとinformation_schemaを除外する。

SELECT schema_name, schema_owner
FROM information_schema.schemata
WHERE schema_name NOT LIKE 'pg\_%'
AND schema_name <> 'information_schema'
ORDER BY schema_name;

 schema_name |   schema_owner
-------------+-------------------
 app         | postgres
 public      | pg_database_owner
 report      | app_user
 sales       | postgres
(4 rows)

LIKEのパターン内では_が任意の1文字にマッチするため、pg_を前方一致で指定するにはpg\_%のようにエスケープする。

information_schema.schemataビューのカラム

information_schema.schemataビューには以下のカラムが存在する。

catalog_name: スキーマが存在するデータベース名（常に現在のデータベース）
schema_name: スキーマ名
schema_owner: スキーマの所有者のロール名
default_character_set_catalog: PostgreSQLでは常にNULL
default_character_set_schema: PostgreSQLでは常にNULL
default_character_set_name: PostgreSQLでは常にNULL
sql_path: PostgreSQLでは常にNULL

実際に利用するのはschema_nameとschema_ownerの2つで、残りはPostgreSQLが未対応の機能のためのカラムである。

\dnとinformation_schema.schemataの結果は一致しない

2つの方法は、システムスキーマの扱いと権限による絞り込みの有無が異なる。

information_schema.schemataは、現在のユーザーがアクセスできるスキーマだけを返す。
アクセスできるとは、スキーマの所有ロールのメンバーであるか、USAGEまたはCREATE権限を持つ状態を指す。
スーパーユーザーはすべてのロールのメンバーとして扱われるため、常にすべてのスキーマが見える。
一方\dnは権限に関係なく、システムスキーマを除くすべてのスキーマを表示する。

app・salesスキーマの所有者がpostgresで、reportスキーマの所有者がapp_userの状態で、app_userとして接続すると結果が食い違う。

\dn
      List of schemas
  Name  |       Owner
--------+-------------------
 app    | postgres
 public | pg_database_owner
 report | app_user
 sales  | postgres
(4 rows)

SELECT schema_name, schema_owner
FROM information_schema.schemata
ORDER BY schema_name;

    schema_name     |   schema_owner
--------------------+-------------------
 information_schema | postgres
 pg_catalog         | postgres
 public             | pg_database_owner
 report             | app_user
(4 rows)

app_userが所有するreportと、PUBLICにUSAGE権限があるpublicは表示される。
しかし権限を持たないapp・salesスキーマはinformation_schema.schemataに現れない。

appスキーマにUSAGE権限を付与すると表示されるようになる。

GRANT USAGE ON SCHEMA app TO app_user;

SELECT schema_name FROM information_schema.schemata ORDER BY schema_name;

    schema_name
--------------------
 app
 information_schema
 pg_catalog
 public
 report
(5 rows)

アプリケーションからinformation_schema.schemataでスキーマの存在を判定する場合は、接続ユーザーの権限次第で結果が変わる点に注意する。

pg_namespaceでスキーマ一覧を確認する

権限に関係なくすべてのスキーマを取得したい場合は、pg_namespaceシステムカタログを参照する。
psqlの\dnも内部ではpg_namespaceを参照している。

SELECT nspname, pg_get_userbyid(nspowner) AS owner
FROM pg_namespace
ORDER BY nspname;

      nspname       |       owner
--------------------+-------------------
 app                | postgres
 information_schema | postgres
 pg_catalog         | postgres
 pg_toast           | postgres
 public             | pg_database_owner
 report             | app_user
 sales              | postgres
(7 rows)

nspownerは所有者のOIDのため、pg_get_userbyid関数でロール名に変換する。
参考: 【PostgreSQL】テーブルとデータベースのoidを取得する方法

pg_classと結合すると、スキーマごとのテーブル数も取得できる。

SELECT n.nspname, count(c.oid) AS tables
FROM pg_namespace n
LEFT JOIN pg_class c ON c.relnamespace = n.oid AND c.relkind = 'r'
WHERE n.nspname NOT LIKE 'pg\_%'
AND n.nspname <> 'information_schema'
GROUP BY n.nspname
ORDER BY n.nspname;

 nspname | tables
---------+--------
 app     |      1
 public  |      0
 report  |      0
 sales   |      0
(4 rows)

relkindがrの行が通常のテーブルを表す。
パーティションテーブルの親はrelkindがpのため、親も数える場合はrelkind IN ('r', 'p')を指定する。

スキーマ内のテーブル一覧そのものを取得する方法は以下を参照。
参考: 【PostgreSQL】SQLでテーブル一覧を取得する