PostgreSQL 模式

PostgreSQL - 模式

在 PostgreSQL 中，模式（Schema）是一个命名空间（namespace），其中包含命名数据库对象，例如表（tables）、视图（views）、索引（indexes）、数据类型（data types）、函数（functions）和运算符（operators）。您可以将模式视为数据库中的目录或文件夹，但与文件夹不同的是，它们不能嵌套。

模式是组织和管理数据库的强大功能，尤其适用于大规模应用程序或多租户（multi-tenant）环境。

为什么要使用模式？

组织管理：将相关对象分组到逻辑单元中，使数据库结构更清晰、更易管理。
多租户：允许多个用户或应用程序使用单个数据库而不会发生对象冲突。每个租户可以拥有自己的模式。
权限管理：在模式级别应用不同的安全权限，简化访问控制。
避免冲突：将第三方扩展或应用程序隔离到它们自己的模式中，以防止与您自己的对象发生名称冲突。

创建模式

CREATE SCHEMA 语句用于创建新模式。您还可以选择为模式指定所有者。

语法

CREATE SCHEMA [ IF NOT EXISTS ] schema_name [ AUTHORIZATION user_name ];

IF NOT EXISTS：（可选）如果同名模式已存在，则防止报错。
schema_name：新模式的名称。
AUTHORIZATION user_name：（可选）将模式的所有权分配给指定用户。如果省略，则执行该命令的用户成为所有者。

示例

让我们为在线商店应用程序创建一个名为 ecommerce 的模式。

testdb=# CREATE SCHEMA ecommerce;
CREATE SCHEMA

“CREATE SCHEMA”消息确认模式已成功创建。默认情况下，每个新数据库都有一个 public 模式。当您创建对象而未指定模式时，它们将在当前模式中创建，这由 search_path 决定。

使用模式和 `search_path`

要在新模式中创建表，您必须限定表名：

CREATE TABLE ecommerce.products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price NUMERIC(10, 2) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

为了避免每次都输入 ecommerce.，您可以将该模式添加到会话的 search_path 中。

testdb=# SHOW search_path;
   search_path
-------------------
 "$user", public
(1 row)

testdb=# SET search_path TO ecommerce, public;
SET

testdb=# -- 现在您可以不带模式前缀地创建表了
testdb=# CREATE TABLE customers ( id SERIAL PRIMARY KEY, email VARCHAR(255) UNIQUE NOT NULL );
CREATE TABLE

PostgreSQL 现在将首先在 ecommerce 模式中查找表，然后是 public 模式。

列出模式

您可以使用 psql 命令 \dn 或查询 information_schema 来列出当前数据库中的所有模式。

使用 psql

testdb=# \dn
      List of schemas
        Name        |  Owner
--------------------+----------
 ecommerce          | myuser
 public             | postgres
(2 rows)

使用 SQL 查询

SELECT schema_name FROM information_schema.schemata;

删除模式

DROP SCHEMA 命令从数据库中删除模式。

语法

DROP SCHEMA [ IF EXISTS ] schema_name [ CASCADE | RESTRICT ];

IF EXISTS：（可选）如果模式不存在，则防止报错。
RESTRICT：（默认）如果模式包含任何对象，则拒绝删除。这是一种安全措施。
CASCADE：删除模式及其所有包含的对象（表、函数等）。务必极其谨慎使用，因为它可能导致不可逆的数据丢失。

示例

要删除 ecommerce 模式及其中的所有表：

-- 这将失败，因为模式不为空
testdb=# DROP SCHEMA ecommerce;
ERROR:  cannot drop schema ecommerce because other objects depend on it
DETAIL:  table ecommerce.products depends on schema ecommerce

-- 这将成功，删除模式及其所有内容
testdb=# DROP SCHEMA ecommerce CASCADE;
DROP SCHEMA