PostgreSQL数据库中的模式

前言

模式是数据库领域的一个基本概念，有些数据库把用户和模式合二为一了，而PG中有清晰的模式定义，相对更加灵活自由。用户更加方便。

那么什么是模式呢？模式是数据库中的一个概念，可以将其理解为一个命名空间或目录，不同的模式下可以有相同名称的表、函数等对象而不会产生冲突。提出模式的概念是为了便于管理，只要有权限，各个模式的对象可以互相调用。如果没有模式的概念，那么不同用户或者开发者在创建各自数据库对象命名时可能会发生冲突以及相互干扰等问题，有了模式，就可以避免这些问题。总结起来模式有如下好处：

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

Postgres模式介绍

一个数据库包含一个或者多个模式，模式中又包含了表、函数以及操作符等数据库对象。其中的层级关系是：数据库-模式-对象。需要注意的是，虽然能创建多个数据库实例，但不能同时访问不同数据库中的对象，当需要访问另一个数据库中的表或其他对象时，需要重新连接到这个数据库，而模式却没有此限制，一个用户在连接到一个数据库后，就可以同时访问这个数据库中多个模式的对象。

所以，在使用MySQL或Oracle的用户迁移到Postgres时，需要注意其中的不同之处。

模式的使用

介绍完模式的概念后，我们实际看一下怎么创建模式，以及其使用。

当我们安装完数据库后，或者创建一个新的数据库后，PG都会为我们创建一个名为public模式，会有一个默认的用户一个public模式。如果创建对象时没有指定模式名，则在此创建表等对象时其模式默认都是public模式。

-- 创建数据库
postgres@postgres=# create database shangcheng;
CREATE DATABASE

postgres@postgres=# \c shangcheng;
You are now connected to database "shangcheng" as user "postgres".
-- 当前用户以及当前模式
postgres@shangcheng=# select current_user,current_schema;
 current_user | current_schema 
--------------+----------------
 postgres     | public
(1 row)
-- 模式搜索路径
postgres@shangcheng=# show search_path ;
             search_path             
-------------------------------------
 "$user", public, pg_catalog
(1 row)

-- 创建表时没有指定模式，则默认是public模式
postgres@shangcheng=# create table pt(a int, b int);
CREATE TABLE
postgres@shangcheng=# \d pt
                 Table "public.pt"   -- public模式
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | integer |           |          | 
 b      | integer |           |          | 

我们创建一个新的模式，CREATE SCHEMA xxx：

-- 创建模式
postgres@shangcheng=# create schema bengbu;
CREATE SCHEMA
-- 查看当前模式列表
postgres@shangcheng=# \dn
     List of schemas
     Name     |  Owner   
--------------+----------
 bengbu       | postgres
 public       | postgres
(2 rows)

--创建表示指定模式名
postgres@shangcheng=# create table bengbu.tb(a int, b int);
CREATE TABLE
postgres@shangcheng=# \d bengbu.tb;
                 Table "bengbu.tb"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | integer |           |          | 
 b      | integer |           |          | 

--创建不同模式下，同名的表
postgres@shangcheng=# create table bengbu.pt(a int, b int);
CREATE TABLE
postgres@shangcheng=# \d pt;
                 Table "public.pt"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | integer |           |          | 
 b      | integer |           |          | 

postgres@shangcheng=# \d bengbu.pt;
                 Table "bengbu.pt"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | integer |           |          | 
 b      | integer |           |          | 

当我们要使用一个数据库对象时，需要键入schema_name.object_name的方式定位该对象。当数据库中对象特别多时，应用起来有些不便，对此Postgres中提供了模式搜索路径，当键入对象名时，通过查找搜索路径来判断一个对象究竟在那个模式下，该路径是一个需要查找的模式列表。在搜索路径里找到的第一个表将被当做选定的表。如果搜索路径中没有匹配的表就会报错。

当然搜索路径可以被修改。可通过设置全局变量或者set search_path= xxx,...的方式进行设置。

postgres@shangcheng=# insert into public.pt values(1,1);
INSERT 0 1
postgres@shangcheng=# insert into bengbu.pt values(2,2);
INSERT 0 1
-- 这是因为搜索路径中先匹配到public模式下的pt表
postgres@shangcheng=# select * from pt;
 a | b 
---+---
 1 | 1
(1 row)

-- 设置搜索路径，将bengbu模式放到public前
postgres@shangcheng=# set search_path = "$user", bengbu,public, pg_catalog;
SET
postgres@shangcheng=# show search_path ;
                 search_path                 
---------------------------------------------
 "$user", bengbu, public, pg_catalog
(1 row)
-- 这样匹配的就是bengbu模式下的pt表对象。
postgres@shangcheng=# select * from pt;
 a | b 
---+---
 2 | 2
(1 row)

到这里，你已经了解了Postgres中的模式，在从其他数据库迁移到Postgres时，需要注意到与Oracle以及Mysql中的不同。才能正确的进行使用与迁移。

更多可以阅读《PostgresSQL修炼之道——从小工到专家》，用2周时间通读一遍，有其他数据库基础的话，很快就可以熟悉Postgres了。