PostgreSQLスキーマ設定

PostgreSQLのスキーマについてメモ書き。

概要
スキーマ一覧表示
スキーマの作成
使用中のスキーマを確認
スキーマ変更
スキーマにテーブル作成
スキーマ検索パス
スキーマ削除

概要

PosgtreSQLは「データベース」-「スキーマ」-「テーブル」の階層構造になっている。
デフォルトのスキーマはpublic。

スキーマ一覧表示

スキーマ一覧はメタコマンド「¥dn」で表示する。

admin=# \dn
      List of schemas
  Name  |       Owner       
--------+-------------------
 public | pg_database_owner
(1 row)

スキーマの作成

スキーマの作成はcreate schema文で行う。
以下ではtestスキーマを作成している。

admin=# create schema test;
CREATE SCHEMA
admin=# \dn
      List of schemas
  Name  |       Owner       
--------+-------------------
 public | pg_database_owner
 test   | admin
(2 rows)

使用中のスキーマを確認

current_schemaで使用中のスキーマを確認できる。

admin=# select current_schema;
 current_schema 
----------------
 public
(1 row)

以下show search_pathでスキーマ検索パス(スキーマの検索順)を確認できる。
※ このデータベースにはユーザ名のスキーマが存在しないため、publicが使用中のスキーマとなる。

admin=# show search_path;
   search_path   
-----------------
 "$user", public
(1 row)

スキーマ変更

カレントスキーマを変更。

admin=# set search_path = test;
SET
admin=# select current_schema();
 current_schema 
----------------
 test
(1 row)

もしくは以下。
※ スキーマを明示的に変更した場合、スキーマ検索パスは変更したスキーマになる。

admin=# set search_path to public;
SET
admin=# show search_path;
 search_path 
-------------
 public
(1 row)

スキーマにテーブル作成

スキーマが別であれば、同じ名前のテーブルを作成できる。

# カレントスキーマはtest
admin=# select current_schema();
 current_schema 
----------------
 test
(1 row)

# test_tableテーブルを作成
admin=# create table test_table (t_id integer, t_member varchar(100));
CREATE TABLE

# ¥dtメタコマンド使用し、testスキーマにテーブルが作成された事を確認
admin=# \dt
          List of relations
 Schema |    Name    | Type  | Owner 
--------+------------+-------+-------
 test   | test_table | table | admin
(1 row)

# publicスキーマを指定(public.test_table)し、同一名称のテーブルを作成
create table public.test_table (t_id integer, t_member varchar(100));

# publicにtest_tableテーブルが作成された事を確認
admin=# \dt public.*
          List of relations
 Schema |    Name    | Type  | Owner 
--------+------------+-------+-------
 public | test_table | table | admin
(1 row)

スキーマ検索パス

オブジェクトを作成するデフォルトのスキーマは、postgresql.confファイルのsearch_pathパラメータで設定する。
先頭から順に実在するスキーマを検索する。

# $userはユーザ名と同じスキーマ。postgresql.confの設定を確認
$ grep search_path postgresql.conf
search_path = '"$user", public'	# schema names

# PostgreSQLにログインし、スキーマ検索パスにtestスキーマを設定
admin=# set search_path to test;
SET
admin=# show search_path;
 search_path 
-------------
 test
(1 row)

# 一旦ログアウトし、再度PostgreSQLにログイン
admin=# exit
$ psql -U admin
psql (15.1 (Debian 15.1-1.pgdg110+1))
Type "help" for help.

# スキーマ検索パスがデフォルト設定に戻る
admin=# show search_path;
   search_path   
-----------------
 "$user", public
(1 row)

postgresql.confファイルのsearch_pathパラメータ変更は、pg_ctl reloadコマンドで反映。
※ pg_settingsのcontextがuserなので、pg_ctl reloadコマンドでOK。

admin=# select name,context from pg_settings where name like 'search_path';
    name     | context 
-------------+---------
 search_path | user
(1 row)

スキーマ削除

スキーマの削除はdrop schema文で行う。

# cascadeを指定しテーブルごと削除
admin=# drop schema if exists test cascade;
NOTICE:  drop cascades to table test.test_table
DROP SCHEMA
admin=# \dn
      List of schemas
  Name  |       Owner       
--------+-------------------
 public | pg_database_owner
(1 row)