TDSQL PostgreSQL版
有奖捉虫：办公协同&微信生态&物联网文档专题 HOT
文档中心 > TDSQL PostgreSQL版 > 开发指南 > 数据库基础开发 > 库模式表索引视图物化视图等 DDL 操作 > 模式管理

模式管理

最近更新时间：2022-09-02 16:23:03

我的收藏

本页目录：

模式本质上是一个名字空间，Oracle 里一般叫用户，SQL Server 中叫框架，MySQL 中叫数据库，模式里面包含表、数据类型、函数以及操作符，对象名称可以与在其他模式中存在的对象重名，访问某个模式中的对象时可以使用"模式名.对象名"。

创建模式

标准语句
postgres=# create schema tdsql_pg;
CREATE SCHEMA
扩展语法，不存在时才创建
postgres=# create schema if not exists tdsql_pg ;  
NOTICE:  schema "tdsql_pg" already exists, skipping
CREATE SCHEMA
postgres=#
指定所属用户
 postgres=# create schema tdsql_pg_pgxz AUTHORIZATION pgxz;
 CREATE SCHEMA
 postgres=# \\dn tdsql_pg_pgxz
List of schemas
 Name   | Owner 
 ------------+-------
tdsql_pg_pgxz | pgxz
 (1 row)

修改模式属性

修改模式名
 postgres=# alter schema tdsql_pg rename to tdsql_pg_new;
 ALTER SCHEMA
修改所有者
 postgres=# alter schema tdsql_pg_pgxz owner to tdsql_pg;
 ALTER SCHEMA

删除模式

postgres=# drop schema tdsql_pg_new;
DROP SCHEMA
当模式中存在对象时，则会删除失败，提示如下。
 postgres=# create table tdsql_pg_pgxz.t(id int);
 NOTICE:  Replica identity is needed for shard table, please add to this table through "alter table" command.
 CREATE TABLE
 postgres=# drop schema tdsql_pg_pgxz;
 ERROR:  cannot drop schema tdsql_pg_pgxz because other objects depend on it
 DETAIL:  table tdsql_pg_pgxz.t depends on schema tdsql_pg_pgxz
 HINT:  Use DROP ... CASCADE to drop the dependent objects too.
参考如下强制删除。
 postgres=# drop schema tdsql_pg_pgxz CASCADE;
 NOTICE:  drop cascades to table tdsql_pg_pgxz.t
 DROP SCHEMA

配置用户访问模式权限

普通用户对于某个模式下的对象访问除了访问对象要授权外，模式也需要授权。
 [tbase@VM_0_37_centos root]$ psql -U dbadmin
 psql (PostgreSQL 10.0 tdsql_pg V2)
 Type "help" for help.

 postgres=# create table tdsql_pg.t(id int);
 NOTICE:  Replica identity is needed for shard table, please add to this table through "alter table" command.
 CREATE TABLE
授权用户可以访问 tdsql_pg.t 表。
postgres=# grant select on tdsql_pg.t to pgxz;
GRANT
postgres=# \\q
pgxz 用户登录。
[tbase@VM_0_37_centos root]$ psql -U pgxz
psql (PostgreSQL 10.0 tdsql_pg V2)
Type "help" for help.
在没授权用户可以使用 tdsql_pg 模式前，还是无法访问。
postgres=> select * from tdsql_pg.t;
ERROR:  permission denied for schema tdsql_pg
LINE 1: select * from tdsql_pg.t;
           ^
postgres=> \\q
给 schema 授权。
[tbase@VM_0_37_centos root]$ psql -U dbadmin
psql (PostgreSQL 10.0 tdsql_pg V2)
Type "help" for help.

postgres=# grant USAGE on SCHEMA tdsql_pg to pgxz;
GRANT
postgres=# \\q
pgxz 用户再次访问表。
[tbase@VM_0_37_centos root]$ psql -U pgxz
psql (PostgreSQL 10.0 tdsql_pg V2)
Type "help" for help.

postgres=> select * from tdsql_pg.t;
 id 
\\----
(0 rows)

postgres=>

配置访问模式的顺序

TDSQL PostgreSQL版 有一个运行变量叫 search_path，其值为模式名列表，用于配置访问数据对象的顺序，如下所示。
当前连接用户。
 postgres=# select current_user;
  current_user 
 --------------
  tdsql_pg
 (1 row)

总共三个模式。
postgres=# \\dn
  List of schemas
   Name   | Owner 
--------------+-------
 public    | dbadmin
 tdsql_pg    | dbadmin
 tdsql_pg_schema | dbadmin
(3 rows)

搜索路径只配置为 "$user", public，其中 "$user" 为当前用户名，即上面的 current_user 值 “tdsql_pg”。
 postgres=# show search_path ;
   search_path  
 -----------------
  "$user", public
 (1 row)
不指定模式创建数据表，则该表存放于第一个搜索模式下面。
 postgres=# create table t(id int,mc text);
 NOTICE:  Replica identity is needed for shard table, please add to this table through "alter table" command.
 CREATE TABLE
 postgres=# \\dt
     List of relations
  Schema | Name | Type  | Owner 
 --------+------+-------+-------
  tdsql_pg | t   | table | dbadmin
 (1 row)
指定表位于某个模式下，不同模式下表名可以相同。
 postgres=# create table public.t(id int,mc text);
 NOTICE:  Replica identity is needed for shard table, please add to this table through "alter table" command.
 CREATE TABLE
 postgres=# \\dt public.t 
     List of relations
  Schema | Name | Type  | Owner 
 --------+------+-------+-------
  public | t   | table | dbadmin
 (1 row)
postgres=# create table tdsql_pg_schema.t1(id int,mc text);    
NOTICE:  Replica identity is needed for shard table, please add to this table through "alter table" command.
CREATE TABLE
postgres=#
访问不在搜索路径对象时，需要写全路径。
postgres=# select * from t1;
ERROR:  relation "t1" does not exist
LINE 1: select * from t1;
           ^
postgres=# select * from tdsql_pg_schema.t1;
 id | mc 
----+----
(0 rows)

上面出错是因为模式 tdsql_pg_schema 没有配置在 search_path 搜索路径中。
中国站