Postgresql 中文操作指南

5.9. Schemas #

PostgreSQL 数据库集群包含一个或多个已命名数据库。角色和少数其他对象类型在整个集群中共享。客户端连接到服务器只能访问单个数据库中的数据（在连接请求中指定的数据）。

A PostgreSQL database cluster contains one or more named databases. Roles and a few other object types are shared across the entire cluster. A client connection to the server can only access data in a single database, the one specified in the connection request.

Note

群集用户不一定有权访问群集中的每个数据库。角色名共享意味着在相同群集中的两个数据库中不能有不同角色名，比如 joe；但是，可以将系统配置为仅允许 joe 访问某些数据库。

Users of a cluster do not necessarily have the privilege to access every database in the cluster. Sharing of role names means that there cannot be different roles named, say, joe in two databases in the same cluster; but the system can be configured to allow joe access to only some of the databases.

数据库包含一个或多个已命名的 schemas，后者又包含表。模式还包含其他类型的命名对象，包括数据类型、函数和运算符。可以在不同的模式中使用相同的对象名称且不会产生冲突；例如，schema1 和 myschema 都可以包含名为 mytable 的表。与数据库不同，模式并不是严格分开的：用户可以访问他们连接到的数据库中任何模式中的对象，如果他们具有这样做所需的权限。

A database contains one or more named schemas, which in turn contain tables. Schemas also contain other kinds of named objects, including data types, functions, and operators. The same object name can be used in different schemas without conflict; for example, both schema1 and myschema can contain tables named mytable. Unlike databases, schemas are not rigidly separated: a user can access objects in any of the schemas in the database they are connected to, if they have privileges to do so.

使用模式可能有多个原因：

There are several reasons why one might want to use schemas:

模式类似于操作系统级别的目录，不同之处在于模式无法嵌套。

Schemas are analogous to directories at the operating system level, except that schemas cannot be nested.

5.9.1. Creating a Schema #

要创建一个架构，请使用 CREATE SCHEMA 命令。为架构指定一个您选择的名称。例如：

To create a schema, use the CREATE SCHEMA command. Give the schema a name of your choice. For example:

CREATE SCHEMA myschema;

要在模式中创建或访问对象，请编写一个 qualified name，它由模式名称和表名称组成，两者之间用点号分隔：

To create or access objects in a schema, write a qualified name consisting of the schema name and table name separated by a dot:

schema.table

这适用于需要表名称的任何地方，包括表修改命令和后续章节中讨论的数据访问命令。（为简洁起见，我们将只讨论表，但相同的概念适用于其他类型的命名对象，例如类型和函数。）

This works anywhere a table name is expected, including the table modification commands and the data access commands discussed in the following chapters. (For brevity we will speak of tables only, but the same ideas apply to other kinds of named objects, such as types and functions.)

实际上，更通用的语法

Actually, the even more general syntax

database.schema.table

也可以使用，但目前这只是为了形式上符合 SQL 标准。如果您编写数据库名称，则必须与您连接到的数据库相同。

can be used too, but at present this is just for pro forma compliance with the SQL standard. If you write a database name, it must be the same as the database you are connected to.

因此，要在新模式中创建表，请使用：

So to create a table in the new schema, use:

CREATE TABLE myschema.mytable (
 ...
);

要删除空模式（其中的所有对象已被删除），请使用：

To drop a schema if it’s empty (all objects in it have been dropped), use:

DROP SCHEMA myschema;

要删除包含所有包含对象的模式，请使用：

To drop a schema including all contained objects, use:

DROP SCHEMA myschema CASCADE;

有关此机制背后的常规描述，请参见 Section 5.14。

See Section 5.14 for a description of the general mechanism behind this.

您通常希望创建一个归其他人所有的模式（因为这是将用户的活动限制在明确定义的名称空间中的方法之一）。语法如下：

Often you will want to create a schema owned by someone else (since this is one of the ways to restrict the activities of your users to well-defined namespaces). The syntax for that is:

CREATE SCHEMA schema_name AUTHORIZATION user_name;

您甚至可以省略架构名称，在这种情况下，架构名称将与用户名相同。有关这样做如何有用，请参阅 Section 5.9.6。

You can even omit the schema name, in which case the schema name will be the same as the user name. See Section 5.9.6 for how this can be useful.

以 pg_ 开头的模式名称是为系统目的保留的，不能由用户创建。

Schema names beginning with pg_ are reserved for system purposes and cannot be created by users.

5.9.2. The Public Schema #

在前面的章节中，我们创建了表，但未指定任何模式名称。默认情况下，此类表（和其他对象）将自动放入名为“public”的模式中。每个新数据库都包含此类模式。因此，以下两者是等效的：

In the previous sections we created tables without specifying any schema names. By default such tables (and other objects) are automatically put into a schema named “public”. Every new database contains such a schema. Thus, the following are equivalent:

CREATE TABLE products ( ... );

及：

and:

CREATE TABLE public.products ( ... );

5.9.3. The Schema Search Path #

限定名称书写起来很繁琐，而且通常最好不要将特定模式名称连接到应用程序中。因此，表通常通过 unqualified names 引用，其中仅包含表名称。系统确定哪个表通过遵循 search path 来指代哪个表，该表是由要在其中查找的模式列表。搜索路径中的第一个匹配表将被视为想要的表。如果搜索路径中没有匹配项，则即使数据库中其他模式存在匹配的表名也会报告错误。

Qualified names are tedious to write, and it’s often best not to wire a particular schema name into applications anyway. Therefore tables are often referred to by unqualified names, which consist of just the table name. The system determines which table is meant by following a search path, which is a list of schemas to look in. The first matching table in the search path is taken to be the one wanted. If there is no match in the search path, an error is reported, even if matching table names exist in other schemas in the database.

在不同模式中创建同名对象的能力使得每次都准确引用相同对象的查询编写变得复杂。这也为用户打开了一个改变其他用户查询行为的大门，无论是出于恶意还是无意。由于在查询以及 PostgreSQL 内部使用了不合格名称，所以向 search_path 中添加模式会有效信任所有对该模式具有 CREATE 权限的用户。当您运行普通查询时，能够在搜索路径的模式中创建对象的恶意用户可以控制并执行任意 SQL 函数，就像您自己执行它们一样。

The ability to create like-named objects in different schemas complicates writing a query that references precisely the same objects every time. It also opens up the potential for users to change the behavior of other users' queries, maliciously or accidentally. Due to the prevalence of unqualified names in queries and their use in PostgreSQL internals, adding a schema to search_path effectively trusts all users having CREATE privilege on that schema. When you run an ordinary query, a malicious user able to create objects in a schema of your search path can take control and execute arbitrary SQL functions as though you executed them.

搜索路径中命名的第一个模式称为当前模式。除了是第一个被搜索的模式之外，它也是在 CREATE TABLE 命令未指定模式名称时将创建新表所在的模式。

The first schema named in the search path is called the current schema. Aside from being the first schema searched, it is also the schema in which new tables will be created if the CREATE TABLE command does not specify a schema name.

要显示当前搜索路径，请使用以下命令：

To show the current search path, use the following command:

SHOW search_path;

在默认设置中，这会返回：

In the default setup this returns:

 search_path
--------------
 "$user", public

第一个元素指定要搜索名称与当前用户相同的模式。如果不存在此类模式，则会忽略该条目。第二个元素引用我们已经看到的公共模式。

The first element specifies that a schema with the same name as the current user is to be searched. If no such schema exists, the entry is ignored. The second element refers to the public schema that we have seen already.

搜索路径中存在的第一个模式是创建新对象时的默认位置。这就是默认情况下在公共模式中创建对象的原因。当在任何其他上下文中引用对象而没有模式限定（表修改、数据修改或查询命令）时，将遍历搜索路径，直到找到匹配的对象。因此，在默认配置中，任何不合格的访问都只能再次引用公共模式。

The first schema in the search path that exists is the default location for creating new objects. That is the reason that by default objects are created in the public schema. When objects are referenced in any other context without schema qualification (table modification, data modification, or query commands) the search path is traversed until a matching object is found. Therefore, in the default configuration, any unqualified access again can only refer to the public schema.

要将我们的新模式放入路径，我们使用：

To put our new schema in the path, we use:

SET search_path TO myschema,public;

（我们在此处省略 $user，因为我们不需要它。）然后，我们可以访问该表，而无需模式限定：

(We omit the $user here because we have no immediate need for it.) And then we can access the table without schema qualification:

DROP TABLE mytable;

此外，由于 myschema 是路径中的第一个元素，因此默认情况下将在其中创建新对象。

Also, since myschema is the first element in the path, new objects would by default be created in it.

我们还可以编写：

We could also have written:

SET search_path TO myschema;

然后，我们不能再不显式限定的情况下访问公共模式。公共模式的特殊之处在于它默认存在。它也可以删除。

Then we no longer have access to the public schema without explicit qualification. There is nothing special about the public schema except that it exists by default. It can be dropped, too.

有关处理架构搜索路径的其他方法，请参阅 Section 9.26。

See also Section 9.26 for other ways to manipulate the schema search path.

搜索路径对数据类型名称、函数名称和运算符名称的工作方式与其对表名称的工作方式相同。数据类型和函数名称可以与表名称完全相同的方式进行限定。如果您需要在表达式中编写限定的运算符名称，则有一个特殊规定：您必须编写

The search path works in the same way for data type names, function names, and operator names as it does for table names. Data type and function names can be qualified in exactly the same way as table names. If you need to write a qualified operator name in an expression, there is a special provision: you must write

OPERATOR(schema.operator)

这是为了避免语法歧义。一个示例是：

This is needed to avoid syntactic ambiguity. An example is:

SELECT 3 OPERATOR(pg_catalog.+) 4;

在实践中，人们通常依赖于操作符的搜索路径，以免编写像那样丑陋的东西。

In practice one usually relies on the search path for operators, so as not to have to write anything so ugly as that.

5.9.4. Schemas and Privileges #

默认情况下，用户无法访问他们不拥有的模式中的任何对象。为了允许这样做，模式的所有者必须授予_USAGE_特权。默认情况下，每个人都对模式_public_拥有此特权。为了允许用户使用模式中的对象，可能需要根据对象的具体情况授予额外的特权。

By default, users cannot access any objects in schemas they do not own. To allow that, the owner of the schema must grant the USAGE privilege on the schema. By default, everyone has that privilege on the schema public. To allow users to make use of the objects in a schema, additional privileges might need to be granted, as appropriate for the object.

还可以允许用户在其他人架构中创建对象。为了实现此目的，需要授予架构上的 CREATE 特权。对于从 PostgreSQL 14 或更早版本升级的数据库，每个人在架构 public 上都拥有该特权。有些 usage patterns 号召取消该特权：

A user can also be allowed to create objects in someone else’s schema. To allow that, the CREATE privilege on the schema needs to be granted. In databases upgraded from PostgreSQL 14 or earlier, everyone has that privilege on the schema public. Some usage patterns call for revoking that privilege:

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

（第一个“public”是架构，第二个“public”是指“每位用户”。在第一种意义上，它是一个标识符；在第二种意义上，它是一个关键字，因此采用不同的首字母大小写；请回顾 Section 4.1.1 中的准则。）

(The first “public” is the schema, the second “public” means “every user”. In the first sense it is an identifier, in the second sense it is a key word, hence the different capitalization; recall the guidelines from Section 4.1.1.)

5.9.5. The System Catalog Schema #

除了_public_和用户创建的模式之外，每个数据库都包含一个_pg_catalog_模式，其中包含系统表以及所有内置数据类型、函数和运算符。pg_catalog_始终有效地是搜索路径的一部分。如果没有在路径中显式命名它，那么就会隐式搜索_before，即搜索路径的模式。这可确保始终可以找到内置名称。不过，如果您愿意让用户定义的名称覆盖内置名称，则可以将_pg_catalog_显式放在搜索路径的末尾。

In addition to public and user-created schemas, each database contains a pg_catalog schema, which contains the system tables and all the built-in data types, functions, and operators. pg_catalog is always effectively part of the search path. If it is not named explicitly in the path then it is implicitly searched before searching the path’s schemas. This ensures that built-in names will always be findable. However, you can explicitly place pg_catalog at the end of your search path if you prefer to have user-defined names override built-in names.

由于系统表名称以 pg 开头，最好避免使用此类名称，以确保如果某个未来版本定义的系统表与您的表同名，您不会遇到冲突。（使用默认搜索路径，对表名称的非限定引用将解析为系统表。）系统表将继续遵循名称以 pg 开头的约定，使得只要用户避免使用_pg__ 前缀，它们就不会与没有限定符的用户表名称冲突。

Since system table names begin with pg, it is best to avoid such names to ensure that you won’t suffer a conflict if some future version defines a system table named the same as your table. (With the default search path, an unqualified reference to your table name would then be resolved as the system table instead.) System tables will continue to follow the convention of having names beginning with pg, so that they will not conflict with unqualified user-table names so long as users avoid the pg_ prefix.

5.9.6. Usage Patterns #

模式可以以多种方式用于组织您的数据。A secure schema usage pattern 阻止不受信任的用户更改其他用户的查询行为。如果数据库不使用安全的模式使用模式，希望安全查询该数据库的用户将在每次会话开始时采取保护措施。具体来说，他们将从设置_search_path_开始每个会话，使之为空字符串，或以其他方式从_search_path_中删除非超级用户可写的模式。默认配置可以轻松支持一些使用模式：

Schemas can be used to organize your data in many ways. A secure schema usage pattern prevents untrusted users from changing the behavior of other users' queries. When a database does not use a secure schema usage pattern, users wishing to securely query that database would take protective action at the beginning of each session. Specifically, they would begin each session by setting search_path to the empty string or otherwise removing schemas that are writable by non-superusers from search_path. There are a few usage patterns easily supported by the default configuration:

对于任何模式，要安装共享应用程序（供每个人使用的表、第三方提供的其他函数等），请将它们放入单独的模式。记得授予适当的特权，以允许其他用户访问它们。然后，用户可以通过使用模式名称限定名称来引用这些其他对象，或者根据他们的选择将其其他模式放入其搜索路径中。

For any pattern, to install shared applications (tables to be used by everyone, additional functions provided by third parties, etc.), put them into separate schemas. Remember to grant appropriate privileges to allow the other users to access them. Users can then refer to these additional objects by qualifying the names with a schema name, or they can put the additional schemas into their search path, as they choose.

5.9.7. Portability #

在 SQL 标准中，不存在同个架构中的对象由不同用户拥有的概念。此外，某些实现不允许您创建与其所有者名称不同的架构。事实上，在仅实现标准中指定的架构基础支持的数据库系统中的架构和用户概念几乎相等。因此，许多用户认为限定名称真实地包含 user_name.table_name 。如果您为每个用户创建一个特定用户的架构，则 PostgreSQL 将有效地行为如何。

In the SQL standard, the notion of objects in the same schema being owned by different users does not exist. Moreover, some implementations do not allow you to create schemas that have a different name than their owner. In fact, the concepts of schema and user are nearly equivalent in a database system that implements only the basic schema support specified in the standard. Therefore, many users consider qualified names to really consist of user_name_._table_name. This is how PostgreSQL will effectively behave if you create a per-user schema for every user.

此外，SQL 标准中没有 public 模式的概念。为了最大程度地符合标准，您不应该使用 public 模式。

Also, there is no concept of a public schema in the SQL standard. For maximum conformance to the standard, you should not use the public schema.

当然，一些 SQL 数据库系统可能根本不实现模式，或者通过允许（可能有限的）跨数据库访问来提供命名空间支持。如果您需要使用这些系统，那么完全不使用模式将实现最大的可移植性。

Of course, some SQL database systems might not implement schemas at all, or provide namespace support by allowing (possibly limited) cross-database access. If you need to work with those systems, then maximum portability would be achieved by not using schemas at all.