PostgreSQL: スキーマの使い方

前提条件

ユーザー scott
データベース　city
でログインできるとします。

$ psql -U scott city
psql (15.2)
"help"でヘルプを表示します。

city=>

ユーザーとデータベースを作成する方法

su postgres
psql < create.sql

create.sql

create user scott with password 'tiger123';
create database city;
alter database city owner to scott;

local からの接続時にパスワードを不要にする設定

/etc/postgresql/15/main/pg_hba.conf

（省略）
local   all             all                                     trust
（省略）

スキーマとテーブルの作成

データベース　city 内に、
次の３つのスキーマを作成します。

ibaraki
tochigi
gunma

それぞれに、cities というテーブルを作成します。

create.sql

create schema ibaraki;
create table ibaraki.cities (id varchar(10) primary key, name text, population int, date_mod date);
insert into ibaraki.cities values ('t0821','水戸',72814,'2011-6-14');
insert into ibaraki.cities values ('t0822','土浦',72814,'2011-7-21');
-----
create schema tochigi;
create table tochigi.cities (id varchar(10) primary key, name text, population int, date_mod date);
insert into tochigi.cities values ('t0931','宇都宮',72814,'2012-4-10');
insert into tochigi.cities values ('t0932','小山',72814,'2012-8-24');
-----
create schema gunma;
create table gunma.cities (id varchar(10) primary key, name text, population int, date_mod date);
insert into gunma.cities values ('t1041','前橋',72814,'2013-9-18');
insert into gunma.cities values ('t1042','高崎',72814,'2013-7-21');

実行

psql -U scott city < create.sql

結果の確認

$ psql -U scott city
psql (15.2)
"help"でヘルプを表示します。

city=> select * from ibaraki.cities;
  id   | name | population |  date_mod  
-------+------+------------+------------
 t0821 | 水戸 |      72814 | 2011-06-14
 t0822 | 土浦 |      72814 | 2011-07-21
(2 行)

city=> select * from tochigi.cities;
  id   |  name  | population |  date_mod  
-------+--------+------------+------------
 t0931 | 宇都宮 |      72814 | 2012-04-10
 t0932 | 小山   |      72814 | 2012-08-24
(2 行)

city=> select * from gunma.cities;
  id   | name | population |  date_mod  
-------+------+------------+------------
 t1041 | 前橋 |      72814 | 2013-09-18
 t1042 | 高崎 |      72814 | 2013-07-21
(2 行)

city=> 

スキーマの表示

city=> \dn
        スキーマ一覧
  名前   |      所有者       
---------+-------------------
 gunma   | scott
 ibaraki | scott
 public  | pg_database_owner
 tochigi | scott
(4 行)

テーブルの表示

city-> \dt *.cities
           リレーション一覧
 スキーマ |  名前  |  タイプ  | 所有者 
----------+--------+----------+--------
 gunma    | cities | テーブル | scott
 ibaraki  | cities | テーブル | scott
 public   | cities | テーブル | scott
 tochigi  | cities | テーブル | scott
(4 行)

スキーマ毎のテーブルの表示

city-> \dt ibaraki.*
           リレーション一覧
 スキーマ |  名前  |  タイプ  | 所有者 
----------+--------+----------+--------
 ibaraki  | cities | テーブル | scott
(1 行)

city-> \dt gunma.*
           リレーション一覧
 スキーマ |  名前  |  タイプ  | 所有者 
----------+--------+----------+--------
 gunma    | cities | テーブル | scott
(1 行)

city-> \dt ibaraki.*
           リレーション一覧
 スキーマ |  名前  |  タイプ  | 所有者 
----------+--------+----------+--------
 ibaraki  | cities | テーブル | scott
(1 行)

スキーマの削除

drop.sql

drop schema ibaraki cascade;
drop schema tochigi cascade;
drop schema gunma cascade;

実行

psql -U scott city < drop.sql

現在のスキーマの表示

select current_schema();

スキーマの変更

set search_path = tochigi