PostgreSQL 模式 (SCHEMA) 详解：数据库对象命名空间管理

2. 在模式中创建对象

标准格式需要显式指定模式名：

CREATE TABLE schema_name.table_name (
    column1 datatype [constraints],
    column2 datatype [constraints],
    ...
);

实际示例：

-- 在 myschema 中创建公司表
CREATE TABLE myschema.company(
    ID INT NOT NULL,
    NAME VARCHAR(20) NOT NULL,
    AGE INT NOT NULL,
    ADDRESS CHAR(25),
    SALARY DECIMAL(18,2),
    PRIMARY KEY(ID)
);

3. 查看模式信息

-- 查看所有模式
\dn 

-- 查看特定模式下的表
\dt myschema.*

-- 查询系统目录
SELECT * FROM information_schema.schemata;

4. 修改模式

-- 更改模式名称
ALTER SCHEMA myschema RENAME TO new_schema;

-- 更改模式所有者
ALTER SCHEMA myschema OWNER TO new_owner;

5. 删除模式

-- 删除空模式
DROP SCHEMA myschema;

-- 强制删除模式及其所有对象
DROP SCHEMA myschema CASCADE;

-- 安全删除（如果存在）
DROP SCHEMA IF EXISTS myschema CASCADE;

模式操作流程

在实际操作中，删除模式前通常需要检查依赖关系。以下是标准的处理逻辑：

flowchart TD
    Start([开始]) --> Check{是否删除？}
    Check -- 是 --> CheckPerm{是否有权限？}
    CheckPerm -- 否 --> Error([报错终止])
    CheckPerm -- 是 --> CheckEmpty{是否为空？}
    CheckEmpty -- 是 --> DirectDrop[直接删除]
    CheckEmpty -- 否 --> AskCascade{使用 CASCADE？}
    AskCascade -- 是 --> Recurse[递归删除所有对象]
    AskCascade -- 否 --> Error
    DirectDrop --> UpdateDB[更新系统表]
    Recurse --> UpdateDB
    UpdateDB --> End([结束])

搜索路径 (Search Path) 机制

PostgreSQL 通过搜索路径来确定对象的位置。如果不指定模式，数据库会按照顺序查找。

-- 查看当前搜索路径
SHOW search_path;
-- 默认值通常是 "$user", public

-- 设置搜索路径
SET search_path TO myschema, public;

查找逻辑：

1. 优先查找 $user 模式（即当前用户名对应的模式）。
2. 接着查找 myschema 模式。
3. 最后查找 public 模式。
4. 如果仍未找到，则抛出错误。

经验之谈：在生产环境中，建议明确设置 search_path，避免依赖默认的 public 模式，这样可以减少解析开销并防止意外调用到同名对象。

最佳实践建议

命名规范

• 使用小写字母和下划线组合，如 hr_data。
• 避免使用 pg_ 前缀，这是保留给系统使用的。

生产环境建议

• 为每个应用创建独立模式。
• 定期清理未使用的模式。
• 尽量避免在 public 模式中创建业务表，这有助于提升安全性。

性能考虑

• 跨模式查询会有轻微的性能开销。
• 合理设置 search_path 可以减少解析开销。

权限与安全

Public 模式的特殊性质

• 所有用户默认拥有 CREATE 和 USAGE 权限。

权限继承规则

• 模式权限不自动继承给其中的对象。
• 表需要单独授权或使用 ALTER DEFAULT PRIVILEGES。

在生产环境中，建议撤销 public 的创建权限：

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

授权示例

-- 授权用户使用模式
GRANT USAGE ON SCHEMA myschema TO user1;

-- 授权表操作权限
GRANT SELECT, INSERT ON ALL TABLES IN SCHEMA myschema TO user1;

常见问题解决方案

问题 1：对象找不到

错误：relation "table1" does not exist

解决：

-- 明确指定模式
SELECT * FROM myschema.table1;
-- 或设置搜索路径
SET search_path TO myschema;

问题 2：权限不足

错误：permission denied for schema myschema

解决：

GRANT USAGE ON SCHEMA myschema TO current_user;

问题 3：删除被拒

错误：cannot drop schema because other objects depend on it

解决：

DROP SCHEMA myschema CASCADE;

总结

PostgreSQL 的模式机制提供了强大的数据库对象组织能力。通过合理使用模式，可以实现多租户隔离、提高对象管理效率、增强数据库安全性并避免命名冲突。

掌握模式的创建、管理和使用技巧，是 PostgreSQL 数据库管理的重要基础。在实际应用中，建议结合业务需求设计合理的模式结构，并配合适当的权限控制，构建安全高效的数据库环境。