Schema 管理
# PostgreSQL Schema 簡介
在 PostgreSQL 中，**Schema（綱要）** 可視為資料庫中的命名空間（namespace），用來組織資料表、檢視表、函數、序列等資料庫物件。
同一個資料庫中可以有多個 Schema，例如：
```text
mydb
├── public
├── sales
├── accounting
└── reporting
```
不同 Schema 可以包含相同名稱的物件，例如：
```text
sales.orders
accounting.orders
```
其中：
- `sales`、`accounting` 是 Schema
- `orders` 是資料表
- 完整名稱通常寫成 `schema.object`
## Schema 與 Database 的差異
| 項目 | Database | Schema |
|---|---|---|
| 層級 | 較高層級 | Database 內的命名空間 |
| 內容 | 包含多個 Schema | 包含資料表、函數等物件 |
| 連線 | 通常需連線到特定 Database | 同一連線中可使用多個 Schema |
| 隔離程度 | 較高 | 主要用於分類、命名與權限管理 |
---
# 一、新增 Schema
## 1. 建立基本 Schema
```sql
CREATE SCHEMA sales;
```
建立後，可使用以下方式建立資料表：
```sql
CREATE TABLE sales.orders (
order_id BIGSERIAL PRIMARY KEY,
customer_name TEXT NOT NULL,
order_date DATE NOT NULL
);
```
也可以查詢：
```sql
SELECT * FROM sales.orders;
```
## 2. 建立 Schema 並指定擁有者
```sql
CREATE SCHEMA accounting AUTHORIZATION accounting_user;
```
這會建立 `accounting` Schema，並將擁有者設定為 `accounting_user`。
執行者通常需要具備建立 Schema 的權限，或具有適當的資料庫權限。
## 3. 建立 Schema 時同時建立物件
```sql
CREATE SCHEMA reporting
AUTHORIZATION report_user
CREATE TABLE report_log (
log_id BIGSERIAL PRIMARY KEY,
message TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
```
## 4. 避免 Schema 已存在時發生錯誤
```sql
CREATE SCHEMA IF NOT EXISTS sales;
```
---
# 二、修改 Schema
PostgreSQL 使用 `ALTER SCHEMA` 修改 Schema 的設定。
## 1. 重新命名 Schema
```sql
ALTER SCHEMA sales RENAME TO sales_data;
```
之後原本的：
```sql
sales.orders
```
會改為：
```sql
sales_data.orders
```
重新命名 Schema 通常需要具備該 Schema 的擁有權。
## 2. 變更 Schema 擁有者
```sql
ALTER SCHEMA sales_data OWNER TO new_owner;
```
例如：
```sql
ALTER SCHEMA sales OWNER TO sales_admin;
```
新的擁有者必須是有效的角色（role），且執行者需具備足夠權限。
## 3. 設定 Schema 的使用權限
### 允許某個角色使用 Schema
```sql
GRANT USAGE ON SCHEMA sales TO analyst;
```
`USAGE` 代表角色可以存取 Schema 中的物件名稱，但不代表可以讀取資料表內容。
### 允許角色在 Schema 中建立物件
```sql
GRANT CREATE ON SCHEMA sales TO developer;
```
### 允許角色查詢某個資料表
```sql
GRANT SELECT ON TABLE sales.orders TO analyst;
```
也可以一次授予 Schema 中目前所有資料表的查詢權限：
```sql
GRANT SELECT ON ALL TABLES IN SCHEMA sales TO analyst;
```
注意：這通常只套用到目前已存在的資料表。若要讓未來建立的資料表也自動套用權限，可設定預設權限：
```sql
ALTER DEFAULT PRIVILEGES IN SCHEMA sales
GRANT SELECT ON TABLES TO analyst;
```
## 4. 設定搜尋路徑 `search_path`
PostgreSQL 會依照 `search_path` 尋找未寫出 Schema 名稱的物件。
查看目前設定：
```sql
SHOW search_path;
```
例如：
```text
"$user", public
```
將 `sales` 加入搜尋路徑：
```sql
SET search_path TO sales, public;
```
之後可以直接寫：
```sql
SELECT * FROM orders;
```
而不必寫：
```sql
SELECT * FROM sales.orders;
```
若要設定某個角色的預設搜尋路徑：
```sql
ALTER ROLE analyst SET search_path TO reporting, public;
```
若要設定某個資料庫的預設搜尋路徑：
```sql
ALTER DATABASE mydb SET search_path TO sales, public;
```
實務上建議在重要程式或 SQL 中使用完整名稱，例如：
```sql
SELECT * FROM sales.orders;
```
這樣較不容易因 `search_path` 改變而查詢到錯誤的物件。
---
# 三、刪除 Schema
## 1. 刪除空的 Schema
```sql
DROP SCHEMA sales;
```
只有在 Schema 中沒有任何物件時，才能直接刪除。
## 2. 避免 Schema 不存在時發生錯誤
```sql
DROP SCHEMA IF EXISTS sales;
```
## 3. 連同 Schema 內所有物件一起刪除
```sql
DROP SCHEMA sales CASCADE;
```
`CASCADE` 會刪除：
- Schema 本身
- Schema 內的資料表
- 檢視表
- 函數
- 序列
- 其他相依物件
例如：
```sql
DROP SCHEMA reporting CASCADE;
```
這個操作可能造成大量資料遺失，執行前應確認 Schema 名稱及內容。
若不希望刪除相依物件，可使用預設的 `RESTRICT`：
```sql
DROP SCHEMA reporting RESTRICT;
```
若 Schema 中仍有物件或其他相依關係，操作會失敗。
---
# 四、常見使用範例
## 建立應用程式 Schema
```sql
CREATE SCHEMA app AUTHORIZATION app_user;
CREATE TABLE app.users (
user_id BIGSERIAL PRIMARY KEY,
username TEXT NOT NULL UNIQUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
```
## 建立報表 Schema 並授予查詢權限
```sql
CREATE SCHEMA reporting;
GRANT USAGE ON SCHEMA reporting TO report_user;
GRANT SELECT ON ALL TABLES IN SCHEMA reporting TO report_user;
```
## 將資料表從一個 Schema 移到另一個 Schema
雖然不是修改 Schema 本身，但常與 Schema 管理一起使用：
```sql
ALTER TABLE public.orders
SET SCHEMA sales;
```
移動後，資料表名稱會從：
```text
public.orders
```
變成：
```text
sales.orders
```
---
# 五、注意事項
1. PostgreSQL 通常會在建立資料庫時自動建立 `public` Schema。
2. 使用 `DROP SCHEMA ... CASCADE` 前應先確認其中的所有物件。
3. `USAGE` 權限不等於資料表的 `SELECT` 權限，兩者通常都需要授予。
4. 物件名稱可使用完整格式：
```sql
schema_name.object_name
```
5. Schema 名稱若包含大寫、空格或特殊字元，必須使用雙引號，但通常建議使用小寫加底線，例如：
```sql
CREATE SCHEMA sales_data;
```
6. Schema 是資料庫內的命名與權限管理單位，不是獨立的資料庫，也不提供完整的資料庫層級隔離。
相關學習地圖、教學課程
Python 資料工程