search_path类似于linux中的path环境变量
postgres=# show search_path;
search_path
-----------------
"$user", public
(1 row)
默认值是$user,public,意思就是当以某个用户登录到数据库的时候,默认就是先查找和登录用户同名的schema,再查找public
例如当前我使用postgres用户连接到msgdb下,查看当前有哪些表:
postgres=# \c msgdb
You are now connected to database "msgdb" as user "postgres".
msgdb=# \dt
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | t1 | table | postgres
public | t2 | table | postgres
这里只显示了public下的两张表,因为msgdb下现在没有postgres这个schema
msgdb=# \dn
List of schemas
Name | Owner
--------+----------
public | postgres
suq | suq
如果我手工设置search_path为suq,那么就可以看到suq这个scheam下的表了,而且你所有的操作都默认在suq下
msgdb=# set search_path=suq;
SET
msgdb=# \dt
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
suq | t2 | table | suq
suq | t3 | table | postgres
suq | t4 | table | suq
suq | t5 | table | suq
suq | tt | table | postgres
msgdb=# create table t1 (id int);
CREATE TABLE
msgdb=# \dt
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
suq | t1 | table | postgres
suq | t2 | table | suq
suq | t3 | table | postgres
suq | t4 | table | suq
suq | t5 | table | suq
suq | tt | table | postgres
为了兼容其他数据库,例如oracle,我们一般将schema和用户名设置为一致,这样当我以此用户登录数据库的时候,默认就是在当前schema下进行操作,
这样有利于数据库的操作,而且也更合理
msgdb=# \c msgdb suq
You are now connected to database "msgdb" as user "suq".
msgdb=# \dt
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
suq | t1 | table | postgres
suq | t2 | table | suq
suq | t3 | table | postgres
suq | t4 | table | suq
suq | t5 | table | suq
suq | tt | table | postgres
postgres=# show search_path;
search_path
-----------------
"$user", public
(1 row)
默认值是$user,public,意思就是当以某个用户登录到数据库的时候,默认就是先查找和登录用户同名的schema,再查找public
例如当前我使用postgres用户连接到msgdb下,查看当前有哪些表:
postgres=# \c msgdb
You are now connected to database "msgdb" as user "postgres".
msgdb=# \dt
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | t1 | table | postgres
public | t2 | table | postgres
这里只显示了public下的两张表,因为msgdb下现在没有postgres这个schema
msgdb=# \dn
List of schemas
Name | Owner
--------+----------
public | postgres
suq | suq
如果我手工设置search_path为suq,那么就可以看到suq这个scheam下的表了,而且你所有的操作都默认在suq下
msgdb=# set search_path=suq;
SET
msgdb=# \dt
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
suq | t2 | table | suq
suq | t3 | table | postgres
suq | t4 | table | suq
suq | t5 | table | suq
suq | tt | table | postgres
msgdb=# create table t1 (id int);
CREATE TABLE
msgdb=# \dt
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
suq | t1 | table | postgres
suq | t2 | table | suq
suq | t3 | table | postgres
suq | t4 | table | suq
suq | t5 | table | suq
suq | tt | table | postgres
为了兼容其他数据库,例如oracle,我们一般将schema和用户名设置为一致,这样当我以此用户登录数据库的时候,默认就是在当前schema下进行操作,
这样有利于数据库的操作,而且也更合理
msgdb=# \c msgdb suq
You are now connected to database "msgdb" as user "suq".
msgdb=# \dt
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
suq | t1 | table | postgres
suq | t2 | table | suq
suq | t3 | table | postgres
suq | t4 | table | suq
suq | t5 | table | suq
suq | tt | table | postgres