在 PostgreSQL 中，模式（Schema） 是数据库对象的逻辑分组，类似于文件系统中的文件夹。它允许将表、视图、索引、存储过程等对象进行分类管理，提高数据库的组织性和安全性。

---

1. 模式（Schema）的作用

• 命名空间：防止不同用户或应用的表重名问题。例如，可以在 sales 模式下创建 orders 表，在 hr 模式下也创建 orders 表，互不冲突。
• 访问控制：可以通过权限管理不同模式，限制用户对不同模式的访问。
• 更好的组织结构：适用于多租户架构或模块化设计。

---

2. PostgreSQL 模式（Schema）相关操作

2.1 创建模式（CREATE SCHEMA）

创建一个新的模式：

CREATE SCHEMA schema_name;

示例：

CREATE SCHEMA sales;

这将在数据库中创建一个名为 sales 的模式。

创建模式并指定所有者

CREATE SCHEMA hr AUTHORIZATION user1;

• 这样 hr 模式的所有者是 user1，其他用户无法默认访问。

---

2.2 查看数据库中的所有模式

要查看 PostgreSQL 数据库中的所有模式，可以使用以下 SQL 语句：

SELECT schema_name FROM information_schema.schemata;

或者：

\dn

在 psql 终端中运行此命令将列出所有模式。

---

2.3 在特定模式下创建表

在 sales 模式下创建 orders 表：

CREATE TABLE sales.orders (
    order_id SERIAL PRIMARY KEY,
    customer_name VARCHAR(100),
    amount DECIMAL(10,2)
);

注意：

• 如果没有指定模式，则表会默认创建在 public 模式下。

---

2.4 使用模式中的表

有两种方式访问模式中的表：

1. 带模式名称访问

SELECT * FROM sales.orders;

2. 设置默认搜索路径

SET search_path TO sales;
SELECT * FROM orders;

这样就不需要在查询时写 sales.orders，而可以直接用 orders。

查看当前搜索路径：

SHOW search_path;

---

2.5 删除模式（DROP SCHEMA）

删除模式但不删除其中对象

DROP SCHEMA schema_name RESTRICT;

如果 schema_name 里面还有对象，这条命令会报错，防止误删。

删除模式及其所有对象

DROP SCHEMA schema_name CASCADE;

示例：

DROP SCHEMA sales CASCADE;

这将删除 sales 模式及其中的所有表、视图等对象。

---

2.6 修改模式所有者

ALTER SCHEMA schema_name OWNER TO new_owner;

示例：

ALTER SCHEMA hr OWNER TO admin_user;

这样 hr 模式的所有者变更为 admin_user。

---

2.7 复制（克隆）模式

PostgreSQL 没有直接提供 COPY SCHEMA 语句，但可以手动复制：

1. 创建新模式

CREATE SCHEMA new_schema;

2. 复制所有表

CREATE TABLE new_schema.orders AS TABLE sales.orders;

3. 复制其他对象（如索引、视图、函数等）
   需手动创建。

---

3. PostgreSQL 默认 public 模式

• PostgreSQL 安装后，默认有一个 public 模式，所有用户都可以访问。
• 如果想要禁止 public 模式的访问：

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

这样普通用户不能再在 public 模式下创建对象。

---

4. PostgreSQL 模式（Schema）权限管理

4.1 授予用户访问模式的权限

GRANT USAGE ON SCHEMA schema_name TO user_name;

示例：

GRANT USAGE ON SCHEMA sales TO user1;

这样 user1 可以使用 sales 模式中的对象，但不能创建新对象。

4.2 允许用户创建对象

GRANT CREATE ON SCHEMA schema_name TO user_name;

示例：

GRANT CREATE ON SCHEMA sales TO user1;

这样 user1 可以在 sales 模式下创建表等对象。

4.3 撤销权限

REVOKE USAGE ON SCHEMA schema_name FROM user_name;

示例：

REVOKE USAGE ON SCHEMA sales FROM user1;

---

5. PostgreSQL 多模式（Multi-Schema）应用

5.1 多租户数据库设计

对于 SaaS（软件即服务）应用，每个租户（客户）可以有自己的模式：

CREATE SCHEMA tenant1;
CREATE SCHEMA tenant2;

然后在 tenant1、tenant2 内部创建自己的 users、orders 表：

CREATE TABLE tenant1.users (
    user_id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);
CREATE TABLE tenant2.users (
    user_id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

这样 tenant1.users 和 tenant2.users 互不影响。

5.2 将模式用作不同模块的组织方式

• sales：存放销售数据的表
• hr：存放人事数据的表
• finance：存放财务数据的表

查询 sales 的 orders 表：

SELECT * FROM sales.orders;

---

6. 总结

操作	SQL 语法
创建模式	CREATE SCHEMA schema_name;
创建模式并指定所有者	CREATE SCHEMA schema_name AUTHORIZATION user_name;
查看模式	SELECT schema_name FROM information_schema.schemata; 或 \dn
删除模式（有对象时报错）	DROP SCHEMA schema_name RESTRICT;
删除模式（级联删除所有对象）	DROP SCHEMA schema_name CASCADE;
在模式内创建表	CREATE TABLE schema_name.table_name (...);
查询模式中的表	SELECT * FROM schema_name.table_name;
设置默认模式	SET search_path TO schema_name;
修改模式所有者	ALTER SCHEMA schema_name OWNER TO new_owner;
授予模式访问权限	GRANT USAGE ON SCHEMA schema_name TO user_name;
授予创建对象权限	GRANT CREATE ON SCHEMA schema_name TO user_name;
撤销权限	REVOKE USAGE ON SCHEMA schema_name FROM user_name;

---

7. 结论

• PostgreSQL Schema（模式） 是数据库对象的命名空间，类似于文件夹。
• 通过 CREATE SCHEMA 创建新模式，可在其中创建表、视图等对象。
• 通过 SET search_path 可指定默认模式，避免每次查询时使用 schema.table 访问。
• DROP SCHEMA ... CASCADE 可删除模式及所有对象。
• 模式适用于 多租户设计 和 模块化数据库管理。

更多详细内容请关注其他相关文章！