从 SQL Server 过渡到 PostgreSQL：理解模式的差异

前言

随着越来越多的企业转向开源技术，商业数据库管理员和开发者也逐渐面临向 PostgreSQL 迁移的需求。 虽然 SQL Server 和 PostgreSQL 共享许多数据库管理系统（RDBMS）的基本概念，但它们在处理某些结构上的差异可能会让人感到困惑，其中最显著的就是模式（Schema）的概念。

在 SQL Server 中，模式提供了一种灵活的对象组织方式，虽然和 PostgreSQL 的模式结构有一定相似性，但两者在用户、权限和对象管理的细节上存在一些重要区别。

什么是 Schema（模式）？

在我们探讨 SQL Server 和 PostgreSQL 之间的区别之前，先简单定义一下“模式”。在数据库系统中，模式是数据库对象（如表、视图、索引、存储过程等）的集合。模式为这些对象提供了一个逻辑命名空间，确保每个对象在该命名空间内是唯一的。 虽然 SQL Server 和 PostgreSQL 都使用模式来组织数据库对象，但它们之间的关系以及模式在实际管理中的作用存在一些显著的区别。

SQL Server 的模式方法：灵活跟用户松耦合

• 模式作为命名空间

在 SQL Server 中，模式提供了一种将对象组织在一起的方式，并且独立于用户账户。每个模式都可以由多个用户访问，而不像一些其他的 RDBMS 那样与用户一一对应紧耦合，例如 Oracle。SQL Server 中的模式不仅是一个逻辑分组，还通过这种方式为数据库提供了更灵活的权限管理。

例如，SQL Server 中默认的 dbo（Database Owner）模式就是一个常见的命名空间，几乎所有用户都可以在该模式中创建对象。此外，SQL Server 允许数据库管理员为不同的功能或部门创建不同的模式，例如 HR.Employees 或 Sales.Customers，从而使对象在同一个数据库中逻辑分离，方便管理和权限分配。

• 对象的完全限定名

在 SQL Server 中，表或视图的完全限定名通常使用 schema_name.object_name 的格式，例如 dbo.Employees。这种命名方式允许数据库管理员按功能或业务部门组织数据库对象，而无需将每个模式与用户严格绑定，也就是说模式跟用户不是一对一关系，另外也可以忽略不写模式，而直接写对象名 object_name。

PostgreSQL 的模式方法：灵活的命名空间模型

• 模式作为命名空间

与 SQL Server 类似，PostgreSQL 中的模式也是命名空间，但与用户账户完全解耦。PostgreSQL 中的模式可以包含多个用户创建的对象，而多个模式中的对象也可以被同一用户拥有或访问。它为数据库提供了模块化的结构，让企业能够根据功能、部门或项目来分配模式。

举个例子，如果在 PostgreSQL 中为 HR 部门创建了一个模式，hr.employees 表的完全限定名将反映该模式和对象的关系，类似 SQL Server 中的 schema_name.object_name 结构。不过，PostgreSQL 允许更细粒度的权限管理，用户可以根据业务需求被授予对特定模式或对象的权限。

• 解耦用户和模式

在 PostgreSQL 中，用户和模式之间没有任何强制绑定关系。一个用户可以跨多个模式拥有对象，而多个用户也可以共享同一个模式。这种灵活性极大地提升了 PostgreSQL 在多用户、多部门协作中的适应能力。

PostgreSQL 允许数据库管理员通过设置搜索路径来简化对象访问，避免在查询中必须总是指定模式名称，跟 SQL Server 一样，可以不必过度依赖用户和模式之间的绑定关系。

SQL Server 和 PostgreSQL 模式的主要区别

1、模式与用户的关系

SQL Server: SQL Server 中的模式独立于用户，用户可以跨多个模式拥有或访问对象。模式是命名空间，主要用于逻辑组织和权限控制。

PostgreSQL: PostgreSQL 同样解耦了模式与用户的关系，多个用户可以拥有同一模式中的对象。比 SQL Server 更加灵活，允许更模块化的数据库设计。

2、模式的创建与管理

SQL Server: 在 SQL Server 中，模式通常是在数据库创建时自动生成（如 dbo），管理员可以显式创建新的模式，并分配给不同的对象。

PostgreSQL: 在 PostgreSQL 中，模式可以通过 CREATE SCHEMA 命令创建，并允许根据需要在单一数据库中创建多个模式，为对象分组提供灵活性。

3、对象组织方式

SQL Server: SQL Server 中，模式用于组织相关对象（如表、视图、存储过程等），这使得管理权限和逻辑分组变得更加简单。模式可以根据业务需求进行自定义命名。

PostgreSQL: PostgreSQL 中的模式也是用于组织数据库对象的逻辑分组。

4、访问控制

SQL Server: SQL Server 的访问控制通过角色和模式实现。用户可以被授予访问特定模式或数据库对象的权限。

PostgreSQL: PostgreSQL 的访问控制也很灵活，支持在模式级别和对象级别进行权限管理。用户可以通过角色拥有跨模式的对象访问权限。

实际操作

1、利用搜索路径

PostgreSQL 的搜索路径功能允许简化查询，避免重复指定模式名称。通过正确配置搜索路径，您可以提高工作效率。

2、使用角色进行权限管理

PostgreSQL 的角色系统为跨多个模式的权限管理提供了极大的灵活性。您可以根据业务需求创建不同的角色，并将相应的访问权限分配给这些角色。

总结

从 SQL Server 过渡到 PostgreSQL 基本上没有太大的差异。在模式在 SQL Server 和 PostgreSQL 中都是用于逻辑分组。

PostgreSQL 跟 SQL Server 一样，拥有模式灵活的特性，而且解耦了用户关系，使得数据库的管理和组织更加模块化。

文章转载自：桦仔

原文链接：https://www.cnblogs.com/lyhabc/p/18440681/sql-server-to-postgresql-understanding-schema-differences

体验地址：http://www.jnpfsoft.com/?from=infoq