1 什么是模式？

模式（schema）是数据库中的一个概念，可以将其理解为一个命名空间或目录，不同的模式下可以有相同名称的表、索引、函数等对象而不会产生冲突。模式，是为了便于管理，只要有权限各个模式的对象可以互相调用。

在PostgreSQL中，一个数据库包含一个或多个模式，模式中又包含了表、函数以及操作等等数据库对象。

在PostgreSQL中，不能同时访问不同数据库中的对象，当要访问另一个数据库中的表或其他对象时，需要重新连接到这个数据库，而模式却没有此限制。一个用户连接到一个数据库后，就可以同时访问这个数据库中的多个模式对象。

从这个特性来说，PostgreSQL的模式概念与MySQL中的Database概念是等价的，在MySQL中也可以同时访问多个Database中的对象，这与PostgreSQL中可以同时访问多个Schema中的对象是一样的。

在Oracle中，一个用户就对应一个Schema。看这里Oracle-11g-创建schema。

需要模式的主要原因：

  • 允许多个用户使用同一个数据库，且用户之间不会互相干扰。
  • 把数据库对象放在不同的模式下组织成逻辑组，使数据库对象更便于管理。
  • 第三方的应用可以放在不同的模式中，这样，就不会和其他对象的名称产生冲突。

PostgreSQL-模式 schema_PostgreSQL

2 如何在PostgreSQL中使用模式？

语法

CREATE SCHEMA schemaname [ AUTHORIZATION username] [ schema_element [... ]]
CREATE SCHEMA AUTHORIZATION username [ schema_element [... ]]

创建模式

create schema baoyw;

PostgreSQL-模式 schema_PostgreSQL_02

查看已有模式

\dn

PostgreSQL-模式 schema_PostgreSQL_03

删除模式

drop schema baoyw;

PostgreSQL-模式 schema_public_04

创建模式（与用户名称相同）

create schema authorization hejp;

PostgreSQL-模式 schema_public_05

创建模式的同时创建表和视图

create schema baoyw
create table t1 (id int,name text)
create table t2 (id int,alias text)
create view v1 as select a.id,a.name,b.alias from t1 a,t2 b where a.id = b.id;

PostgreSQL-模式 schema_public_06

修改模式的名称和属主

语法

# 修改模式的名称
ALTER SCHEMA name RENAME TO newname;

# 修改模式的属主
ALTER SCHEMA name OWNER TO newowner;

示例

# 修改模式的名称
alter schema baoyw rename to baoywold;
# 修改模式的属主
alter schema baoywold owner to hejp;

PostgreSQL-模式 schema_schema_07

3 公共模式

要创建或者访问模式中的对象，就必须按照以下的语法格式：

# 模式名与表名之间，用“.”分开
schema.table_name

通常情况下，创建和访问表都不需要指定模式，那么，在这种情况下，你访问的都是public模式。每当我们创建一个新的数据库时，PostgreSQL都会为我们自动创建一个名为“public”的模式。当你登录到该数据库时，如果没有特意指定，都是以该模式（public模式）操作各种数据对象的。

4 模式的搜索路径

使用数据库对象时，如果每次都使用全称来定位对象，如schema_name.object_name，显示很繁锁。对此，PostgreSQL中提供了模式搜索路径，这种形式有些类似Linux中$PATH环境变量的用法，当我们执行一个shell命令时，只有当该命令位于$PATH的目录列表中时，才可以通过命令名直接执行，否则就需要输入它的全路径名。

在PostgreSQL中，同样也需要通过查找搜索路径来判断一个表究竟是哪个模式下，该路径是一个需要查找的模式列表。在搜索路径里找到的第一个表将被当作选定的表。如果搜索路径中没有匹配的表就会报错，即使匹配表的名称在数据库其他的模式中存在也会如此。

搜索路径中的第一个模式叫做当前模式。除了是搜索的第一个模式之外，它还是在CREATE TABLE没有声明模式名时新建表所属的模式。要显示当前搜索路径，使用以下命令：

show search_path;

上面显示的是search_path的默认路径，从这个默认配置中可以看到public模式总是在搜索路径中。所以一般情况下，或创建的表没有指定模式，就会在public模式下。在psql中使用“\d”命令显示public模式下的表。

\d

PostgreSQL-模式 schema_schema_08

5 模式的权限

默认情况下，用户无法访问模式中不属于它的对象。若要访问此类对象，模式的所有者必须在模式下赋予它“USAGE”权限。为了让用户使用模式中的对象，可能需要赋予适合该对象的额外权限。

用户也可以在别人的模式中创建对象，当然，这需要具有该模式下的CREATE权限。需要注意的是，默认情况下，每个用户在public模式下都有CREATE和USAGE权限，也就是说，允许所有连接到指定数据库上的用户在此模式下创建对象。当然，也可以撤销权限，使用以下命令：

REVOKE CREATE ON SCHEMA PUBLIC FROM PUBLIC;

其中，第一个“PUBLIC”模式的名称，第二个“PUBLIC”的意思是“所有用户”。回收权限后，其他用户就不能在public模式下创建对象了。

模式的权限，实际测试与在“参考”中的描述不一致。这一点需再验证。

PostgreSQL-模式 schema_PostgreSQL_09

6 模式的可移植性

在SQL标准中，同一个模式下的对象是不能被不同的用户拥有的，而且有些数据库系统不允许创建和它们的所有者不同名的模式，比如Oracle数据库。实际上，在那些只实现了标准中规定的基本模式的数据库系统里，模式和用户的概念几乎是一样的，比如Oracle数据库。因此，许多用户考虑对名字加以修饰，使它们真正由“username.tablename”组成。

如果在PostgreSQL中为每个用户都创建一个与用户名同名的模式，那么就能与Oracle兼容了。

同样，在SQL标准中，也没有public模式的概念。为了最大限度地遵循标准，并且与其他数据库兼容（比如Oracle数据库），建议不要使用（甚至是应该删除）public模式。

PostgreSQL-模式 schema_PostgreSQL_10

当然有些数据库系统中，可能根本没有模式，而是通过允许跨库访问来提供模式的功能，如MySQL。

如果需要在这些数据库上实现最大的可移植性，或许不应该使用模式。

假如，MySQL实例中有3个数据库，在移植到PostgreSQL数据库中时，或许你应该创建3个模式，使其与MySQL实例中有3个数据库相对应，而不是在PostgreSQL数据库中创建3个数据库与之对应。


@参考《PostgreSQL修炼之道 从小工到专家》 第2版