Postgresql用户schema与search_path

一、查看当前schemas

warehouse_db=# select current_schemas(true);
   current_schemas   
---------------------
 {pg_catalog,public}
(1 row)

二、当前search_path

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


warehouse_db=# set search_path="$user",public,tank;
SET
warehouse_db=# show search_path;
      search_path      
-----------------------
 "$user", public, tank
(1 row)


warehouse_db=# insert into emp4 values(1)
;
INSERT 0 1
warehouse_db=# select * from emp4;
 id 
----
  1
(1 row)


warehouse_db=# create table emp4(id int);
CREATE TABLE
warehouse_db=# insert into emp4 values(1)
;
INSERT 0 1
warehouse_db=# select * from emp4;
 id 
----
  1
(1 row)


warehouse_db=# select * from tank.emp4;
 id 
----
  1
(1 row)

三、设置tank为优先查找patch
warehouse_db=# set search_path="$user",tank,public;
SET
warehouse_db=# insert into emp4 values(2)
;
INSERT 0 1
warehouse_db=# select * from emp4;
 id 
----
  1
  2
(2 rows)


warehouse_db=# select * from public.emp4;
 id 
----
  1
(1 row)

四、要看表在那个schemaname

warehouse_db=# select tablename,schemaname from pg_tables where tablename in('emp4');
 tablename | schemaname 
-----------+------------
 emp4      | public
 emp4      | tank
(2 rows)

五、设置用户默认 search_patch
postgres=# alter user tank set search_path=tank;
ALTER ROLE
postgres=# \c warehouse_db

warehouse_db=# \c - tank
You are now connected to database "warehouse_db" as user "tank".
warehouse_db=#              show search_path;
 search_path 
-------------
 tank
(1 row)


warehouse_db=# create table tank02 (id int);
CREATE TABLE
warehouse_db=# SELECT schemaname, tablename FROM pg_tables WHERE tablename
IN('emp4','tank02');
 schemaname | tablename 
------------+-----------
 public     | tank02
 public     | emp4
 tank       | emp4
 tank       | tank02
(4 rows)


[postgres@db2 ~]$ psql -d warehouse_db -U tank
psql (9.5.2)
Type "help" for help.


warehouse_db=# show search_path;
 search_path 
-------------
 tank
(1 row)