PostgreSQL 教程: 模式

九月 12, 2023

摘要:在本教程中,您将了解 PostgreSQL 模式以及如何使用模式搜索路径来解析模式中的对象。

什么是 PostgreSQL 模式

在 PostgreSQL 中,模式是一个命名空间,其中包含命名的数据库对象,例如表、视图索引数据类型函数存储过程和运算符。

要访问模式中的对象,您需要使用以下语法限定该对象:

schema_name.object_name

一个数据库可以包含一个或多个模式,每个模式只属于一个数据库。两个模式可以具有相同名称的不同对象。

例如,您可能有包含staff表的sales模式,和也包含staff表的public模式。当您引用staff表时,您必须对其进行如下限定:

public.staff

或者

sales.staff

为什么需要使用模式

有一些场景需要使用模式:

  • 模式允许您将数据库对象(例如表)组织到逻辑组中,以使它们更易于管理。
  • 模式使多个用户能够使用一个数据库而不会互相干扰。

public 模式

PostgreSQL 会自动为每个新数据库创建一个叫做public的模式。无论您在不指定模式名称的情况下创建什么对象,PostgreSQL 都会将其放入此public模式中。因此,以下语句是等效的:

CREATE TABLE table_name(
  ...
);

CREATE TABLE public.table_name(
   ...
);

模式搜索路径

在实践中,您会不带模式名称引用一个表,例如staff表,而不是完全限定的名称(例如sales.staff表)。

当您仅使用表名称引用表时,PostgreSQL 将使用模式搜索路径(即要查找的模式列表)来搜索该表。

PostgreSQL 将访问模式搜索路径中的第一个匹配表。如果没有匹配,它将返回错误,即使该名称存在于数据库中的另一个模式中。

搜索路径中的第一个模式称为当前模式。请注意,当您创建新对象而未显式指定模式名称时,PostgreSQL 还会对新对象使用当前模式。

current_schema()函数返回当前模式:

SELECT current_schema();

这是输出:

 current_schema
----------------
public
(1 row)

这就是 PostgreSQL 用public模式创建您的每个新对象的原因。

要查看当前搜索路径,可以使用psql工具中的SHOW命令:

SHOW search_path;

输出如下:

 search_path
-----------------
"$user", public
(1 row)

在此输出中:

  • "$user"指定 PostgreSQL 将用于搜索对象的第一个模式,该模式与当前用户同名。例如,如果您使用postgres用户登录并访问staff表,PostgreSQL 将在postgres模式中搜索staff表。如果找不到任何类似的对象,它将继续在public模式中查找该对象。
  • 第二个元素指的是我们之前看到的public模式。

要创建新模式,请使用CREATE SCHEMA语句,如下:

CREATE SCHEMA sales;

要将新模式添加到搜索路径,请使用以下命令:

SET search_path TO sales, public;

现在,如果您创建一个名为staff但未指定模式名称的新表,PostgreSQL 会将此staff表放入sales模式中:

CREATE TABLE staff(
    staff_id SERIAL PRIMARY KEY,
    first_name VARCHAR(45) NOT NULL,
    last_name VARCHAR(45) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE
);

下图显示了新的模式sales以及属于该sales模式的staff表:

PostgreSQL Schema Example

要访问sales模式中的staff表,您可以使用以下语句之一:

SELECT * FROM staff;

或者

SELECT * FROM sales.staff;

public模式是搜索路径中的第二个元素,因此要访问public模式中的staff表,必须按如下方式限定表名称:

SELECT * FROM public.staff;

如果使用以下命令,则需要使用完全限定名称显式引用public模式中的对象:

SET search_path TO public;

public模式不是特殊的模式,因此您也可以删除它。

PostgreSQL 模式和权限

用户只能访问他们拥有的模式中的对象。这意味着他们无法访问不属于他们的模式中的任何对象。

要允许用户访问不属于他们的模式中的对象,您必须向用户授予模式的USAGE权限:

GRANT USAGE ON SCHEMA schema_name 
TO role_name;

要允许用户在不属于他们的模式中创建对象,您需要将模式的CREATE权限授予用户:

GRANT CREATE ON SCHEMA schema_name 
TO user_name;

请注意,默认情况下,每个用户在public模式上都有CREATEUSAGE权限。

PostgreSQL 模式操作

  • 要创建新模式,请使用CREATE SCHEMA语句。
  • 要重命名模式或更改其所有者,请使用ALTER SCHEMA语句。
  • 要删除模式,请使用DROP SCHEMA语句。

在本教程中,您了解了 PostgreSQL 模式以及 PostgreSQL 如何使用搜索路径来解析对象名称。