PostgreSQL 之 schema

使用schema的作用:

1、允许多个用户使用一个数据库并且不会互相干扰。
2、将数据库对象组织成逻辑组以便更容易管理。
3、第三方应用的对象可以放在独立的模式中，这样它们就不会与其他对象的名称发生冲突。
4、模式类似于操作系统层的目录，但是模式不能嵌套。

pg 中的schema 与 mysql 、Oracle 之间的差异：

1. PostgreSQL中，一个database下可以有多个schema。可以给schema指定一个owner，如果没有指定，那么当前用户就是schema的默认owner。
2. 在Oracle数据库中不能直接新建一个schema，系统在创建一个用户的同时为这个用户创建一个同名的schem并作为该用户的缺省shcema。即schema的个数同user的个数相同，而且schema名字同user名字一一 对应并且相同。
3. 在MySQL中没有schema，所以创建一个database的效果和建立一个schema是相同的。我们可以简单的理解为，MySQL中的database就是schema。

1、创建/删除schema

-- 需要先建库，schema存在于库之下：
-- 创建用户:
postgres@[local]:5432=# CREATE USER dw WITH PASSWORD 'dw';
NOTICE:  resource queue required -- using default resource queue "pg_default"
CREATE ROLE
-- 创建数据库：dw 指定用户：dw
postgres@[local]:5432=# create database dw with owner=dw;
CREATE DATABASE
-- 创建用户及数据库：dw
postgres@[local]:5432=#\c dw dw
-- 创建Schema:
dw@[local]:5432=>create schema rds;
CREATE SCHEMA
-- 默认是谁创建的schema,owner就是谁,当然也可以指定:authorization 
dw@[local]:5432=>create schema tds authorization dw;
CREATE SCHEMA
-- 不指定schema,则schema名字与owner一致。
dw@[local]:5432=>create schema authorization dw;
CREATE SCHEMA
-- 查看schema:
dw@[local]:5432=>\dn
    Name    |  Owner  
------------+---------
 dw         | dw      -- 不指定schema,则schema名字与owner一致。
 gp_toolkit | gpadmin
 public     | gpadmin
 rds        | dw
 tds        | dw
-- 删除模式
-- 删除一个为空的模式（其中的所有对象已经被删除）：
dw@[local]:5432=>DROP SCHEMA myschema;
-- 删除一个模式以及其中包含的所有对象：
dw@[local]:5432=>DROP SCHEMA myschema CASCADE;

2、修改数据库的模式查找路径

-- 显示模式查找路径
dw@[local]:5432=>\c
You are now connected to database "dw" as user "dw".
dw@[local]:5432=> show search_path;
  search_path   
----------------
 "$user",public
-- 其中user是获取当前用户名，因此“$user”放在前面，切换用户（schema）时，会自动匹配，
-- 如果用户下存在相同名字的模式，则搜索路径也将会包含该模式。
-- 永久修改查询路径：
dw@[local]:5432=>alter database dw set search_path to "$user", public,rds, tds;
ALTER DATABASE
 -- 设置模式查找路径
set search_path to rds;

3、不同schema 之间相互访问：

dw@[local]:5432=>set search_path to rds;
SET
dw@[local]:5432=>create table t1 (id int,name varchar(10));
dw@[local]:5432=>\d
            List of relations
 Schema | Name | Type  | Owner | Storage 
--------+------+-------+-------+---------
 rds    | t1   | table | dw    | heap
dw@[local]:5432=>set search_path to tds;
SET
dw@[local]:5432=>\d
No relations found.
-- tds 访问 rds 下面的表：需要加schema.table_name
dw@[local]:5432=>select * from rds.t1;
 id | name 
----+------
(0 rows)
dw@[local]:5432=>select * from t1;
ERROR:  relation "t1" does not exist
LINE 1: select * from t1;
dw@[local]:5432=>insert into rds.t1 values(1,'a');
INSERT 0 1
dw@[local]:5432=>select * from rds.t1;
 id | name 
----+------
  1 | a

4、修改Schema

-- 修改schema的属主
alter schema test owner to two;     
-- public 改为普通 schema 
-- 修改schema的名称,需要使用gpamdin用户进行修改，仅修改当前库中的public
alter schema  public rename to two;
-- 回收权限
revoke all on schema two from public;