PostgreSQL中的schema和user

postgresql中,用户创建的所有对象都被创建在指定的schema(或namespace)中。其他用户可能拥有、也可能不拥有访问这些对象的权限,甚至都不可以在对应的schema中创建对象。

PostgreSQL中的schema和user

从上面的表可以看出,用户(或角色)是全局对象,不是定义在数据库中,而是定义在实例的级别。schema是用户在指定的数据库中创建的,其中包含数据库对象。

 

可能有人对下面的操作感到困惑:

$ psql
psql (11.9)
Type "help" for help.

postgres=# create table t1(a int);
CREATE TABLE
postgres=# 

在这个创建表的语句中,并没有指定schema。那这些表会在哪里呢?

每个postgresql数据库默认都有一个public schema,如果没有为对象显式地指定schema,新创建的对象就是放在public schema中。有几种方式可以查看给定的表是属于哪个schema,但是最常用的主要有两种:1.查看系统目录视图;2.使用psql短命令

postgres=# select schemaname from pg_tables where tablename =‘t1‘;
 schemaname 
------------
 public
(1 rows)

postgres=# \d t1
                 Table "public.t1"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | integer |           |          | 

postgres=# 

在postgresql中,public schema是一个特殊的schema,你要么移除该schema,要么收回在public schema上授予给public的权限。

 

当你删除了public schema后尝试创建一个表,会发生什么呢?

postgres=# drop schema public cascade;
NOTICE:  drop cascades to table t1
DROP SCHEMA
postgres=# create table t1 ( a int );
ERROR:  no schema has been selected to create in
LINE 1: create table t1 ( a int );
                     ^
postgres=# 

  


我们现在没有schema了:

postgres=# \dn
List of schemas
 Name | Owner 
------+-------
(0 rows)

postgresql不知道去哪里创建表。这里也可以看出,在postgresql中schema和user是不同的了。我们是以postgres用户登录,但是没有schema可以创建对象。

 

现在,我们来创建一个schema:

postgres=# create schema my_schema;
CREATE SCHEMA
postgres=# create table t1(a int);
ERROR:  no schema has been selected to create in
LINE 1: create table t1(a int);
                     ^
postgres=# 

仍然不可以创建表。这里可能会问:为什么有public schema就可以呢?在上面的演示中我们并没有指定public schema呀!这里就需要提到参数search_path了:

postgres=# show search_path;
   search_path   
-----------------
 "$user", public
(1 row)

postgres=# 

缺省情况下,search_path包含当前用户名和public。所以会创建失败。

有两种方式来解决:

postgres=# create table my_schema.t1 ( a int );
CREATE TABLE
postgres=# \d my_schema.t1
               Table "my_schema.t1"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 a      | integer |           |          | 

或者调整search_path的值:

postgres =  # set search_path = ‘my_schema‘,"$user",public;
SET
postgres =  # show search_path ;
search_path
----------------------------
my_schema, "$user", public
(1 row)

postgres =  # create table t2 ( a int );
CREATE TABLE
postgres =  # \d t2
				Table "my_schema.t2"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 a      | integer |           |          | 

postgres =  # 

  

PostgreSQL中的schema和user

上一篇:Oracle expdp 导出和 impdp 导入


下一篇:mysql 函数的使用 之 group_concat