PostgreSQL 基础知识：模式(schema)是什么？

体验【开源中国】全新APP，一站式掌握开源技术动态，即刻下载，开启您的开源技术探索之旅！

在 PostgreSQL 中组织数据的一种方法是使用模式。PostgreSQL 中的模式是什么？更重要的是：模式的目的是什么以及如何使用模式让您的生活更轻松？让我们深入了解一下。

模式的目的

在弄清楚如何使用模式之前，您需要首先了解模式的用途。要理解这一点，首先看一下 PostgreSQL 的结构：

•实例

•数据库

•模式

•表

•列

“实例”基本上就是您在部署 PostgreSQL 时启动的。下一层是数据库。实际上，这就是您要连接的对象：在 PostgreSQL 中，连接始终绑定到实例内的数据库，这发生在用户身份验证之后的早期。

重要的是数据库和表之间的一层：模式。

模式组表

基本上，模式是一种将表分组在一起的方法。

让我们假设有一个相当大的数据结构：在一个地方有 500 个表肯定比有 10 个桶，每个桶包含 50 个表更难管理和理解。

这就像组织图片一样：您不会将所有图片都放在同一个文件夹中，而是按年份、位置等对它们进行分组。相同的逻辑可以应用于表格。

模式和 PostgreSQL

现在我们可以关注如何将这个概念应用于 PostgreSQL。我们首先要看的是public模式。

使用“公共”（public）模式

PostgreSQL 的美妙之处在于，即使您对模式一无所知也没关系。原因是public模式的存在，默认情况下存在。我们如何找出 PostgreSQL 中有哪些模式？

psql 提供了\dn显示此信息的命令：

demo=# \dn      List of schemas  Name  |       Owner       --------+------------------- public | pg_database_owner(1 row)

在默认情况下，表将最终出现在public模式中。这是一个例子：

demo=# CREATE TABLE t_product (id      serial, name    text, price   numeric);CREATE TABLE

这是一个基本表。该表可以在public模式中找到。\d将揭示真相：

demo=# \d              List of relations Schema |       Name       |   Type   | Owner --------+------------------+----------+------- public | t_product        | table    | hs public | t_product_id_seq | sequence | hs(2 rows)

在这种情况下，模式和序列都按预期在默认模式中找到。如您所见，您不需要任何关于模式的知识即可继续。如果您碰巧使用了该public模式，我们还建议您查看最新版本的 PostgreSQL 中引入的新安全策略：https://www.cybertec-postgresql.com/en/error-permission-denied-schema-public/

两种查询表的方法

第一种方法是：

demo=# SELECT * FROM t_product; id | name | price ----+------+-------(0 rows)

查询表的另一种方法：

但是，您也可以显式地使用模式名称作为表名的前缀，这构成了一个完全限定的名称。能够降低由于配置错误而访问错误表的风险。我们稍后还会在这篇文章中看到它：

demo=# SELECT * FROM public.t_product; id | name | price ----+------+-------(0 rows)

在对public模式进行简要介绍之后，我们可以继续创建我们的第一个新模式。

创建模式

我们如何在 PostgreSQL 中创建模式？命令CREATE SCHEMA就是答案：

demo=# \h CREATE SCHEMACommand:     CREATE SCHEMADescription: define a new schemaSyntax:CREATE SCHEMA schema_name [ AUTHORIZATION role_specification ] [ schema_element [ ... ] ]CREATE SCHEMA AUTHORIZATION role_specification [ schema_element [ ... ] ]CREATE SCHEMA IF NOT EXISTS schema_name [ AUTHORIZATION role_specification ]CREATE SCHEMA IF NOT EXISTS AUTHORIZATION role_specification
where role_specification can be:
    user_name  | CURRENT_ROLE  | CURRENT_USER  | SESSION_USER

URL: https://www.postgresql.org/docs/15/sql-createschema.html

语法非常简单，允许我们定义名称和模式所有者。

demo=# CREATE SCHEMA warehouse;CREATE SCHEMA

创建模式后，我们可以在模式中创建一个表：

demo=# CREATE TABLE warehouse.t_product (   prod_number text PRIMARY KEY,    d           date,    in_stock    int);CREATE TABLE

通过使用模式名称作为表名的前缀，您可以定义要使用的模式。请注意，模式本身不会影响数据的存储方式。与我们的表关联的数据文件仍然在同一个 PostgreSQL 数据目录中。因此，模式不会影响性能，也与存储优化无关。模式的目的只是将事物组合在一起，并通过为模式分配权限来帮助组织可靠的安全策略：

demo=# \d warehouse.t_product;              Table "warehouse.t_product"   Column    |  Type   | Collation | Nullable | Default-------------+---------+-----------+----------+--------- prod_number | text    |           | not null |  d           | date    |           |          |  in_stock    | integer |           |          | Indexes:    "t_product_pkey" PRIMARY KEY, btree (prod_number)

这里有两点值得指出：

首先，在两个不同的模式中可以有两个同名的表。有名为public.t_product和warehouse.t_product表是完全可行的，而且实际上很常见。

然后，重要的是我们不必在public模式中为表添加前缀。原因是以下参数：

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

可以直接访问要在search_path参数定义的模式名中的所有数据库对象，而无需显式提供模式名称。我们可以轻松地尝试一下：

demo=# SET search_path TO warehouse;SET

请注意，该参数仅在您的会话中更改——如果您在交互式会话中运行它，它不会破坏您的生产系统。

从现在开始，将显示名为t_product的表，因为 PostgreSQL 知道在哪个模式中查找：

demo=# \d t_product              Table "warehouse.t_product"   Column    |  Type   | Collation | Nullable | Default-------------+---------+-----------+----------+--------- prod_number | text    |           | not null |  d           | date    |           |          |  in_stock    | integer |           |          | Indexes:    "t_product_pkey" PRIMARY KEY, btree (prod_number)

现在search_path已经更改了，我们必须在模式前加上前缀public，因为它不再在路径中：

demo=# \d public.t_product                                Table "public.t_product" Column |  Type   | Collation | Nullable |                   Default                   --------+---------+-----------+----------+-----------------... id     | integer |           | not null | … name   | text    |           |          |  price  | numeric |           |          |

在对模式进行了基本介绍之后，让我们弄清楚将模式与视图结合使用意味着什么？

视图和模式

视图是让开发人员更容易访问数据的好方法。重要的一点是模式通常不是障碍（有关视图和权限的具体细节，请参见此处）。查询可以自由地连接来自不同模式的表，并且使用查询的视图可以在您选择的模式中公开数据（假设您有这样做的权限）：

demo=# SET search_path TO default;SETdemo=# CREATE VIEW public.v ASSELECT * FROM warehouse.t_product ;CREATE VIEW

但是，这对想要从 Oracle 迁移到 PostgreSQL 的人有影响。

提示：查看 CYBERTEC 迁移器

https://www.cybertec-postgresql.com/en/products/cybertec-migrator/

在 PostgreSQL 中重命名模式

在 PostgreSQL 中，所有可以创建的东西都可以重命名。模式也是如此：

demo=# \h ALTER SCHEMACommand:     ALTER SCHEMADescription: change the definition of a schemaSyntax:ALTER SCHEMA name RENAME TO new_nameALTER SCHEMA name OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }

URL: https://www.postgresql.org/docs/15/sql-alterschema.html

重命名模式会产生影响，这些影响在以下内容中进行了概述。注意视图发生了什么：

demo=# ALTER SCHEMA warehouse RENAME TO inventory;ALTER SCHEMAdemo=# \d+ v                                 View "public.v"   Column    |  Type   | Collation | Nullable | Default | Storage  | …-------------+---------+-----------+----------+---------+----------+ … prod_number | text    |           |          |         | extended |  d           | date    |           |          |         | plain    |  in_stock    | integer |           |          |         | plain    | View definition: SELECT t_product.prod_number,    t_product.d,    t_product.in_stock   FROM inventory.t_product;

视图不直接引用表——它引用内部对象 ID，这在这里非常重要，因为重命名模式仅意味着将不同的文本标签附加到内部 ID。视图定义不依赖于名称，因此重命名对象确实会使视图无效。在 Oracle 等数据库中，重命名对象会使视图处于无效状态。

删除模式

在 PostgreSQL 中删除模式遵循相同的逻辑：

demo=# \h DROP SCHEMACommand:     DROP SCHEMADescription: remove a schemaSyntax:DROP SCHEMA [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

URL: https://www.postgresql.org/docs/15/sql-dropschema.html

模式依赖

我们不需要孤立的对象，所以我们不能在没有附带损害的情况下丢弃它们。PostgreSQL 会准确告诉我们发生什么，但不会真正删除表——以避免破坏对模式的依赖：

demo=# DROP SCHEMA inventory;ERROR:  cannot drop schema inventory because other objects depend on itDETAIL:  table inventory.t_product depends on schema inventoryview v depends on table inventory.t_productHINT:  Use DROP ... CASCADE to drop the dependent objects too.

如果我们真的想放弃模式并面对与之相关的所有后果，CASCADE可以添加该选项：

demo=# DROP SCHEMA inventory CASCADE;NOTICE:  drop cascades to 2 other objectsDETAIL:  drop cascades to table inventory.t_productdrop cascades to view vDROP SCHEMA

如您所见，所有依赖对象都已删除，我们留下了一个干净、一致的数据库，其中不包含任何陈旧或无效的对象。有关详细信息，请参阅Laurenz Albe 关于视图依赖项的博客：https://www.cybertec-postgresql.com/en/tracking-view-dependencies-in-postgresql/。

最后 …

即使您没有意识到，模式始终是重要的一部分；它们提供了一种以更易于理解的方式更清晰地组织数据的好方法。有关如何允许其他用户访问特定模式中的对象的更多信息，请参阅有关 ALTER DEFAULT PRIVILEGES 的博客：https://www.cybertec-postgresql.com/en/postgresql-alter-default-privileges-permissions-explained/。