模式本质上是一个名字空间,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, skippingCREATE SCHEMApostgres=#
指定所属用户
postgres=# create schema tdsql_pg_pgxz AUTHORIZATION pgxz;CREATE SCHEMApostgres=# \\dn tdsql_pg_pgxzList of schemasName | 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 TABLEpostgres=# drop schema tdsql_pg_pgxz;ERROR: cannot drop schema tdsql_pg_pgxz because other objects depend on itDETAIL: table tdsql_pg_pgxz.t depends on schema tdsql_pg_pgxzHINT: Use DROP ... CASCADE to drop the dependent objects too.
参考如下强制删除。
postgres=# drop schema tdsql_pg_pgxz CASCADE;NOTICE: drop cascades to table tdsql_pg_pgxz.tDROP SCHEMA
配置用户访问模式权限
普通用户对于某个模式下的对象访问除了访问对象要授权外,模式也需要授权。
[tbase@VM_0_37_centos root]$ psql -U dbadminpsql (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;GRANTpostgres=# \\q
pgxz 用户登录。
[tbase@VM_0_37_centos root]$ psql -U pgxzpsql (PostgreSQL 10.0 tdsql_pg V2)Type "help" for help.
在没授权用户可以使用 tdsql_pg 模式前,还是无法访问。
postgres=> select * from tdsql_pg.t;ERROR: permission denied for schema tdsql_pgLINE 1: select * from tdsql_pg.t;^postgres=> \\q
给 schema 授权。
[tbase@VM_0_37_centos root]$ psql -U dbadminpsql (PostgreSQL 10.0 tdsql_pg V2)Type "help" for help.postgres=# grant USAGE on SCHEMA tdsql_pg to pgxz;GRANTpostgres=# \\q
pgxz 用户再次访问表。
[tbase@VM_0_37_centos root]$ psql -U pgxzpsql (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=# \\dnList of schemasName | Owner--------------+-------public | dbadmintdsql_pg | dbadmintdsql_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 TABLEpostgres=# \\dtList of relationsSchema | 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 TABLEpostgres=# \\dt public.tList of relationsSchema | 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 TABLEpostgres=#
访问不在搜索路径对象时,需要写全路径。
postgres=# select * from t1;ERROR: relation "t1" does not existLINE 1: select * from t1;^postgres=# select * from tdsql_pg_schema.t1;id | mc----+----(0 rows)
上面出错是因为模式 tdsql_pg_schema 没有配置在 search_path 搜索路径中。