schema的创建、设置、删除

schema：是数据库的一个命名空间；或者说是一个容器。
在一个数据库中，同一个 schema下不能创建同名的表；不同的schema可以创建同名的表。

 # 创建一个schema
    postgres=# create schema if not exists schema01 ;
    CREATE SCHEMA
    # 设置到schema01:
    postgres=# set search_path to schema01;
    SET
    # 查看已有的schema:
    postgres=# \dn
       List of schemas
       Name   |  Owner   
    ----------+----------
     public   | postgres
     schema01  | postgres
    (2 rows)
    postgres=# create table t1(id int,name varchar(32));
    CREATE TABLE
    postgres=# select * from t1;
     id | name 
    ----+------
    (0 rows)
    postgres=# create table t1(id int,name varchar(32));
    CREATE TABLE
    postgres=# insert into t1 values(1,'zth');
    INSERT 0 1
    postgres=# select * from t1;
     id | name 
    ----+------
      1 | zth
    (1 row)
    #  切换schema 到 public 下：
    postgres=# set search_path to public;
    SET
    # 会报错 找不到t1表：
    postgres=# select * from t1;
    2019-09-18 15:44:27.289 CST [12778] ERROR:  relation "t1" does not exist at character 15
    2019-09-18 15:44:27.289 CST [12778] STATEMENT:  select * from t1;
    ERROR:  relation "t1" does not exist
    LINE 1: select * from t1;

    再设置回去就好了。
    删除schema:
    postgres=# drop schema schema01;       
    2019-09-18 16:02:55.784 CST [12778] ERROR:  cannot drop schema schema01 because other objects depend on it
    2019-09-18 16:02:55.784 CST [12778] DETAIL:  table t1 depends on schema schema01
    2019-09-18 16:02:55.784 CST [12778] HINT:  Use DROP ... CASCADE to drop the dependent objects too.
    2019-09-18 16:02:55.784 CST [12778] STATEMENT:  drop schema schema01;
    ERROR:  cannot drop schema schema01 because other objects depend on it
    DETAIL:  table t1 depends on schema schema01
    HINT:  Use DROP ... CASCADE to drop the dependent objects too.
    postgres=# \h drop schema              
    Command:     DROP SCHEMA
    Description: remove a schema
    Syntax:
    DROP SCHEMA [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]
    # 由上面的报错我们可以知道：schema 下有表时要进行级联删除，使用关键字cascade:
    postgres=# drop schema schema01 cascade;
    NOTICE:  drop cascades to table t1
    DROP SCHEMA
    
    我们可以在不同层级（数据库、用户、会话）指定schema:
    在用户级别设置：
    postgres=# create user myuser;                                                          
    CREATE ROLE
    postgres=# alter user myuser set search_path = 'mynewschema';
    ALTER ROLE
    [postgres@pg3 data]$ psql -U myuser -d postgres                                         
    psql (13.0)
    Type "help" for help.

    postgres=> show search_path;                                                            
 	search_path 
	-------------
	mynewschema
	(1 row)

	在数据库级别设置schema:
	test=# select current_schema;
 	current_schema 
	----------------
 
	(1 row)

	test=# alter database test set search_path ='mynewschema1';                                                     
	ALTER DATABASE
	test=# select current_schema;
	 current_schema 
	----------------
 
	(1 row)

	test=# \q
	[postgres@pg3 data]$ psql -U postgres -d test
	psql (13.0)
	Type "help" for help.

	test=# select current_schema;
 	current_schema 
	----------------
	 mynewschema1
	(1 row)

	test=# create table emp (id int);
	CREATE TABLE
	test=# \d+
                              List of relations
    Schema    | Name | Type  |  Owner   | Persistence |  Size   | Description 
--------------+------+-------+----------+-------------+---------+-------------
 mynewschema1 | emp  | table | postgres | permanent   | 0 bytes | 
(1 row)

由于可以在不同级别设置schema,我们可能会遇到一些意外情况：
1.在服务器或数据库级别修改schema,现有连接将不会使用新的值；
2.在会话级别中设置search_path,将不会用于其他新的连接。
3.我们可能在数据库级别设置该参数，但将使用户级别设置的参数可能是一个不同的值。