PostgreSQL 模式详解

什么是模式？

模式（Schema）是 PostgreSQL 数据库中用于组织和管理数据库对象的逻辑容器。它类似于操作系统中的文件夹，但不能嵌套，为数据库对象提供了命名空间的管理。

架构概述

PostgreSQL 采用层次化的架构设计，从上到下依次为：

架构层级关系

层级	描述	共享范围	访问限制
集群	包含多个数据库	角色和部分对象类型	全集群共享
数据库	包含多个模式	模式内的所有对象	连接时指定单个数据库
模式	包含表、视图、函数等	同一模式内对象	需要相应权限访问
对象	表、视图、函数、类型等	不共享	基于权限控制

重要特性

• 角色共享性：同一集群中的角色在所有数据库间共享
• 数据库隔离：客户端连接只能访问单个指定数据库
• 模式命名空间：同一模式内不能有同名的同类型对象

模式的基本概念

模式的作用

模式在 PostgreSQL 中发挥着至关重要的作用：

1. 命名空间管理：防止对象名称冲突
2. 权限控制：提供细粒度的访问控制
3. 逻辑组织：将相关对象分组管理
4. 应用隔离：不同应用使用独立模式

对象命名规则

在同一模式内，以下对象类型共享命名空间：

• 表（Table）
• 序列（Sequence）
• 索引（Index）
• 视图（View）
• 物化视图（Materialized View）
• 外部表（Foreign Table）

命名冲突在同一模式中，上述对象类型之间不能有相同的名称。例如，如果已有名为 users 的表，就不能再创建名为 users 的视图。

对象引用语法

PostgreSQL 支持多种对象引用方式：

-- 完全限定名（推荐用于跨模式引用）
database.schema.table

-- 模式限定名（常用方式）
schema.table

-- 非限定名（依赖搜索路径）
table

创建和管理模式

基本创建语法

创建模式的基本语法如下：

-- 基本创建语法
CREATE SCHEMA schema_name;

-- 指定所有者创建
CREATE SCHEMA schema_name AUTHORIZATION user_name;

-- 省略模式名，使用用户名作为模式名
CREATE SCHEMA AUTHORIZATION user_name;

实践示例

让我们通过完整的示例来演示模式的创建和使用：

创建模式

-- 示例1：创建基本模式
CREATE SCHEMA sales;

-- 示例2：为特定用户创建模式
CREATE SCHEMA hr AUTHORIZATION hr_manager;

-- 示例3：创建与用户同名的模式
CREATE SCHEMA AUTHORIZATION alice;

在模式中创建对象

-- 在 sales 模式中创建表
CREATE TABLE sales.products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2),
    category_id INTEGER
);

-- 在 sales 模式中创建视图
CREATE VIEW sales.expensive_products AS
SELECT * FROM sales.products
WHERE price > 1000;

-- 在 hr 模式中创建表
CREATE TABLE hr.employees (
    emp_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    hire_date DATE DEFAULT CURRENT_DATE
);

模式删除操作

-- 删除空模式
DROP SCHEMA sales;

-- 级联删除模式及其所有对象
DROP SCHEMA sales CASCADE;

-- 仅在模式存在时删除
DROP SCHEMA IF EXISTS sales CASCADE;

谨慎使用 CASCADE 使用 CASCADE 选项会删除模式中的所有对象，这个操作不可恢复。在生产环境中使用前请务必备份数据。

模式重命名

-- 重命名模式
ALTER SCHEMA old_name RENAME TO new_name;

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

公共模式

公共模式的特点

public 模式是每个 PostgreSQL 数据库的默认模式，具有以下特性：

• 自动创建：每个新数据库都包含 public 模式
• 默认位置：未指定模式的对象会创建在此
• 公共访问：默认情况下所有用户都有访问权限

公共模式示例

-- 以下两种写法等效
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE public.products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

查看当前模式中的对象

-- 查看当前模式中的所有表
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public';

-- 查看指定模式中的所有表
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'sales';

模式搜索路径

搜索路径概念

搜索路径（Search Path）决定了 PostgreSQL 如何解析非限定的对象名称。它是一个有序的模式列表，系统会按顺序搜索匹配的对象。

查看和设置搜索路径

-- 查看当前搜索路径
SHOW search_path;
-- 输出：search_path
--      --------------
--      "$user", public

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

-- 永久设置用户的搜索路径
ALTER USER username SET search_path TO sales, hr, public;

-- 重置为默认搜索路径
SET search_path TO DEFAULT;

搜索路径实践示例

让我们通过实际例子来理解搜索路径的工作原理：

准备数据

-- 创建多个模式和同名表
CREATE SCHEMA app1;
CREATE SCHEMA app2;

-- 在不同模式中创建同名表
CREATE TABLE public.users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    type VARCHAR(20) DEFAULT 'public_user'
);

CREATE TABLE app1.users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    type VARCHAR(20) DEFAULT 'app1_user'
);

CREATE TABLE app2.users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    type VARCHAR(20) DEFAULT 'app2_user'
);

测试搜索路径

-- 插入测试数据
INSERT INTO public.users (name) VALUES ('Public User');
INSERT INTO app1.users (name) VALUES ('App1 User');
INSERT INTO app2.users (name) VALUES ('App2 User');

-- 测试不同搜索路径的效果
SET search_path TO public;
SELECT * FROM users;  -- 将返回 public.users 的数据

SET search_path TO app1, public;
SELECT * FROM users;  -- 将返回 app1.users 的数据

SET search_path TO app2, app1, public;
SELECT * FROM