玩转PostgreSQL（二）：如何使用schema搜索路径

总结: 在本文中，将学习PostgreSQL schema以及如何使用schema搜索路径、解析schema中的对象。

?PostgreSQL schema是什么

在PostgreSQL中，schema是一个命名空间，其中包含名为database的对象，例如表，视图,索引,数据类型,函数,存储过程和标识符。

要访问schema中的对象，我们需要使用以下语法对对象进行限定:

schema_name.object_name

一个数据库可以包含一个或多个schema，并且每个schema仅属于一个数据库。两个schema可以具有共享相同名称的不同对象。

例如，你可能有testschema ，user 表，public schema也具有user 表。当你提到user 表你必须限定如下:

public.user

Or

test.user

?为什么需要使用schema

?为什么需要使用schema:

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

?public schema

对于每个新数据库，PostgreSQL自动创建一个名为publicschema。无论我们创建的对象有没有指定schema名称，PostgreSQL都会将其放入publicschema。因此，以下语句是等效的:

CREATE TABLE table_name(
  ...
);

和

CREATE TABLE public.table_name(
   ...
);

?schema搜索路径

实际上，引用没有其schema名称的表，例如，user表而不是完全限定的名称，例如test.user表。

当仅使用表的名称引用表时，PostgreSQL使用schema搜索路径,这是要查找的schema列表。

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

搜索路径中的第一个schema称为当前schema。请注意，当我们在未显式指定schema名称的情况下创建新对象时，PostgreSQL还将为新对象使用当前schema。

current_schema()函数返回当前schema:

SELECT current_schema();

Output

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

这就是为什么对于我们创建的每个新对象，PostgreSQL使用public修饰。

在psql工具要查看当前搜索路径，请使用show命令:

SHOW search_path;

Output

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

?在此输出中:

• "$ user"指定PostgreSQL将用于搜索对象的第一个schema，该对象与当前用户具有相同的名称。例如，如果我们使用postgres用户登录并访问user表。PostgreSQL将在postgresschema搜索user表。如果找不到这样的对象，它将继续在publicschema搜索user表。
• 第二个要素是指public我们之前已经看到的模式。

要创建新schema，请使用CREATE SCHEMA声明:

CREATE SCHEMA test;

要将新schema添加到搜索路径，请使用以下命令:

SET search_path TO test, public;

现在，如果我们创建一个名为user不指定schema名称，PostgreSQL将把它user表到testschema:

CREATE TABLE user(
    user_id SERIAL PRIMARY KEY,
    name VARCHAR(45) NOT NULL
);

在testschema访问user表我们可以使用以下语句之一:

SELECT * FROM user;

Or

SELECT * FROM test.user;

public schema是搜索路径中的第二个元素，因此在public schema中要访问user表，我们必须限定表名，如下所示:

SELECT * FROM public.staff;

如果使用以下命令，则需要显式引用public使用完全限定名称的schema:

SET search_path TO public;

? public schema不是特殊的schema， 所以你可以删除他

?PostgreSQL schemas 和 权限

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

要允许用户访问其不拥有的schema中的对象，必须授予USAGEschema权限

GRANT USAGE ON SCHEMA schema_name 
TO role_name;

要允许用户在他们不拥有的schema中创建对象，我们需要向他们授予CREATEschema 权限:

GRANT CREATE ON SCHEMA schema_name 
TO user_name;

? 请注意，默认情况下，在publicschema 每个用户都有CREATE和USAGE。

?PostgreSQL schema 操作

• 要创建新schema，请使用CREATE SCHEMA声明。
• 要重命名schema或更改其所有者，请使用ALTER SCHEMA声明。
• 要删除schema，请使用DELETE SCHEMA声明。

在这之前，我们已经了解了PostgreSQL schema以及PostgreSQL如何使用搜索路径来解析对象名称。

?PostgreSQL CREATE SCHEMA 语句概述

? NOTE: 在此处，我们将学习如何使用PostgreSQLCREATE SCHEMA语句以在数据库中创建新schema。

CREATE SCHEMA 语句允许我们在当前数据库中创建新schema。

创建SCHEMA声明:

CREATE SCHEMA [IF NOT EXISTS] schema_name;

在以下语法中:

• 首先，指定架构后CREATE SCHEMA关键词。schema名称在当前数据库中必须是唯一的。
• 第二，可选使用IF NOT EXISTS只有当新schema不存在时才有条件地创建它。尝试创建已经存在的新schema而不使用IF NOT EXISTS语句将导致错误。

? NOTE:执行CREATE SCHEMA语句，我们必须具有当前数据库中CREATE的权限。

我们还可以为用户创建schema:

CREATE SCHEMA [IF NOT EXISTS] 
AUTHORIZATION username;

在这种情况下，将为username创建schema

PostgreSQL还允许我们创建schema和对象列表，例如使用单个语句创建表和视图，如下所示:

CREATE SCHEMA schema_name
    CREATE TABLE table_name1 (...)
    CREATE TABLE table_name2 (...)
    CREATE VIEW view_name1
        SELECT select_list FROM table_name1;

? NOTE:请注意，每个子命令不以分号 (;) 结尾。

?PostgreSQL CREATE SCHEMA 示例

让我们举一些使用CREATE SCHEMA例子，以获得更好的理解。

?1) 使用CREATE SCHEMA创建新SCHEMA示例

以下语句使用创建SCHEMA语句以创建名为的新schema tests:

CREATE SCHEMA tests;

以下语句返回当前数据库中的所有schema:

SELECT * 
FROM pg_catalog.pg_namespace
ORDER BY nspname;)

?2) 使用CREATE SCHEMA为用户创建schema

首先，创建一个角色 名为 gg

CREATE ROLE gg 
LOGIN
PASSWORD 'Postgr@123#';

第二，为gg创建schema语法如下 :

CREATE SCHEMA AUTHORIZATION gg;

第三，为gg创建一个名为dd的 schema :

CREATE SCHEMA IF NOT EXISTS dd AUTHORIZATION john;

?3) 使用CREATE SCHEMA创建schema及其对象的示例

以下示例使用CREATE SCHEMA语句以创建名为的新schemassm。它还会创建一个名为spring的表和一个名为spring_boot的视图

CREATE SCHEMA ssm

    CREATE TABLE spring(

        id SERIAL NOT NULL,

        version DATE NOT NULL

    )

    CREATE VIEW spring_boot AS

        SELECT ID, version

        FROM spring

        WHERE version