PostgreSQLでは、データベースとテーブルの間にスキーマという階層があるのをご存じでしょうか。通常SQLを実行する際、データベース名を指定するとその下にある全てのテーブルが参照できてしまいます。
しかしスキーマを使えばテーブルをグループ分けしたり、同じ名称のテーブルを作ることも可能な便利な機能です。今回はこのようなPostgreSQLのスキーマという機能について紹介します。
目次
そもそもスキーマとは
スキーマはPostgreSQLで使える機能の1つで、データベースとテーブルの間に位置する階層のようなものです。
しかしデフォルトのスキーマは自動で作られるうえスキーマ名を省略して使えるので、ITエンジニアとして長く働いているもののスキーマを意識したことがない、という方がいるかもしれません。
とはいえPostgreSQLでスキーマを使っていない、ということはPostgreSQLの便利な機能を使っていない、ということです。まずはスキーマとは何かについて解説します。
スキーマについてはこちらの記事でも詳しく解説しています。参照してください。
データベースとスキーマ
PostgreSQLのデータ構造
PostgreSQLでは階層構造によりデータを管理しています。具体的には最も外側にデータベースがあり、SQLを実行するにはデータベースを1つしか選べません。続いて今回紹介しているスキーマがあり、その中にテーブルを作れます。
さらにテーブルは同じ構造のレコードが集まったもので、レコードの中には複数のフィールドがあり、その中にデータが含まれています。この階層構造を簡単に記述すると次のように書くことができます。
データベース > スキーマ > テーブル > レコード > フィールド
このようにスキーマとは、データベースとテーブルの間にある階層です。
デフォルトのスキーマは省略できる
しかし、PostgreSQLを使ったことがある方なら、データベースを作ったら、すぐにその中でテーブルを作れたはずです。
これはPostgreSQLでデータベースを作成すると同時にpublicというスキーマが作成され、さらにスキーマ名を省略するとpublicスキーマの中にテーブルが作られるので、スキーマを意識しなくても利用できるからです。
例えばPostgreSQLの管理ツール「pgadmin」を利用してデータベースをクリックしてみてください。多くの属性等が表示されますが、その中にテーブルはありません。
スキーマ(schema)があるのでそれをクリックしてください。publicというスキーマがあり、その中にテーブルが格納されています。
スキーマでテーブルをグループ化
PostgreSQLでデータを管理する場合、最も外側のデータベースは1つしか選べません。しかし、アカウントや用途によってアクセスできるテーブルを制限したいケースはよくあります。
その場合、テーブル毎にアクセス権限を設定することも可能ですがスマートではありません。その点スキーマでグループ化すれば、もっとスマートな設定が可能です。
PostgreSQLでデータを管理するなら、ぜひスキーマをうまく活用してください。
スキーマの使い方
先ほど紹介したようにスキーマは便利な機能です。次からPostgreSQLのスキーマの使い方について紹介します。
SQLのスキーマの指定方法
スキーマの中に作成したテーブルを参照するSQLを作るには、テーブル名の前にスキーマ名を記述し、「.」で区切ります。
workスキーマの中のtable1テーブルを指定する例
|
1 |
work.table1 |
スキーマ内のテーブルを使ったSQLの例
|
1 2 3 4 |
CREATE TABLE work.table1 ( id int primary key, name text, type int ); INSERT INTO work.table1 VAUES (1, 'name_1', 1 ); INSERT INTO work.table1 VAUES (2, 'name_2', 1 ); SELECT * FROM work.table1; |
これは「work」というスキーマの中に作成したテーブル「table1」にレコードを追加し、そのデータを表示するSQLです。スキーマ内のテーブルの指定に「work.table1」を使っています。
publicスキーマ
先ほど紹介したようにPostgreSQLでデータベースを作成すると自動的にpublicというスキーマが作られます。そしてこのpublicスキーマは全てのユーザーがアクセス可能で、さらにスキーマ名を省略可能です。
publicスキーマを省略したSQLの例
|
1 |
SELECT * from table1; |
上の例は、スキーマ名としてpublicを使った「public.table1」テーブルの全てのデータを表示するSQLと同じです。
publicスキーマを明示したSQLの例
|
1 |
SELECT * from public.table1; |
PostgreSQLのスキーマの管理方法
これまで説明したようにPostgreSQLではデータベースの中に直接テーブルを作成するのではなく、スキーマを作ってその中にテーブルを作成します。
ただし、データベースを作ると自動的にpublicというスキーマが作られて、スキーマを指定しない場合、テーブルやビューなどはpublicの中に作られます。そのため、普段からPostgreSQLを使っているものの、スキーマの存在を忘れている方もいるかもしれません。
スキーマをうまく利用すれば、テーブルを効率良く管理できます。そのためにはスキーマを管理するSQLを知らなければなりません。次から、スキーマを管理するSQLの使い方を解説します。
スキーマを作る
スキーマを作成するにはSQLのCREATEコマンドを使います。なお、新規にスキーマを作成した際、その所有者を指定できるので、必要に応じて指定してください。
スキーマを作成するSQLの文法
CREATE SCHEMA スキーマ名;
CREATE SCHEMA スキーマ名 AUTHORIZATION アカウント名;
スキーマを作成する例
|
1 |
CREATE SCHEMA work AUTHORIZATION worker; |
これはworkというスキーマを、workerというアカウントを所有者として作成する例です。なお作成後、PostgreSQLのクライアントから「\dn」コマンドでチェックできます。
スキーマの中にテーブルを作る
スキーマの中にテーブルを作成する場合、そのSQLでスキーマ名を指定したテーブル名を指定します。具体的には、先ほど紹介したようにテーブル名の前にスキーマ名をドットで指定してください。
スキーマ名付きのテーブル名を使った例
|
1 |
CREATE TABLE work.table1 ( id int primary key, name text, type int ); |
この例ではworkというスキーマの中にtable1というテーブルを作成します。テーブル名の「work.table1」に注目してください。
スキーマ名を変更する
スキーマ名を変更するにはスキーマの構成を変更するSQL、ALTER SCHEMAを使います。
スキーマ名を変更するSQLの文法
ALTER SCHEMA 対象のスキーマ名 RENAME TO 新しいスキーマ名;
スキーマを削除する
不要になったスキーマを削除することも可能です。なお、その場合は中にテーブルが無い状態でなければなりません。そしてスキーマを削除するにはDROP SCHEMAコマンドを使います。
スキーマを削除するSQLの文法
DROP SCHEMA 対象のスキーマ名
スキーマ間を移動するには
スキーマの中にあるテーブルを、別のスキーマに移動させることが可能です。なおスキーマを変更するには、テーブルの構成を変更するSQLのALTER TABLEコマンドを使用します。
スキーマを移動するSQL
ALTER TABLE 対象のテーブル名 SET SCHEMA 移動先のスキーマ名;
スキーマを移動するSQLの例
|
1 |
ALTER TABLE test.table2 SET SCHEMA work; |
これはtestというスキーマの中にあるテーブルtable2を、workというスキーマの中に移動するためのSQLです。
なお、publicというスキーマは特殊でスキーマ名を省略できます。そのため、publicの中にあるテーブルを別のスキーマに移動する場合はpublicを省略可能です。
publicからworkにスキーマを移動するSQLの例
|
1 |
ALTER TABLE table3 SET SCHEMA work; |
まとめ
これまで説明したようにPostgreSQLにはデータベースとテーブルの間にスキーマという階層があり、テーブルをグループ分けすることが可能です。
なおPostgreSQLでデータベースを作成した際、publicというスキーマが作られる、そのスキーマ名を省略できます。そのためスキーマを意識せずにPostgreSQLを利用している方も多いかもしれません。
スキーマを利用すれば同じ名称のテーブルを作れたり、アカウントによってアクセスできるテーブルを制限できるなど、データベースを管理するのに便利な機能です。ぜひ活用してください。
