Обсуждение: why schema name is same as username behaves different then others
Note: test=# super user postgres test=> regular user robot ### comments test=#create schema t AUTHORIZATION robot; CREATE SCHEMA test=> select * from pg_namespace ; nspname | nspowner | nspacl ------------+----------+-------- pg_catalog | 1 | {=U} pg_toast | 1 | {=} public | 1 | {=UC} pg_temp_1 | 1 | t | 1045 | test=> create table foo(test text); CREATE TABLE test=> \dt List of relations Schema | Name | Type | Owner --------+------+-------+------- public | foo | table | robot test=> create table t.foo(test text); CREATE TABLE test=> \dt List of relations Schema | Name | Type | Owner --------+------+-------+------- public | foo | table | robot (1 row) ####I expect to see something like: List of relations Schema | Name | Type | Owner --------+------+-------+------- public | foo | table | robot t | foo | table | robot test=> insert into foo values('zzzz'); INSERT 19868125 1 test=> insert into t.foo values('sssss'); INSERT 19868126 1 test=> select * from foo; test ------ zzzz (1 row) test=# create schema robot AUTHORIZATION robot; CREATE SCHEMA test=> select * from pg_namespace ; nspname | nspowner | nspacl ------------+----------+-------- pg_catalog | 1 | {=U} pg_toast | 1 | {=} public | 1 | {=UC} pg_temp_1 | 1 | t | 1045 | robot | 1045 | (6 rows) test=> create table robot.foo(test text); CREATE TABLE test=> \dt List of relations Schema | Name | Type | Owner --------+------+-------+------- robot | foo | table | robot (1 row) ####I expect to see something like: List of relations Schema | Name | Type | Owner --------+------+-------+------- public | foo | table | robot t | foo | table | robot robot | foo | table | robot #### why it's not? test=> insert into robot.foo values('xxxxx'); INSERT 19868173 1 test=> select * from foo; test ------- xxxxx (1 row) #### why schema t,robot behave different??? #### it seems if schema name is same as username then #### it will shadow the objects in the public schema that have same name. #### I haven't documentation address this yet. Jie Liang Software Engineer St. Bernard Software 16882 W. Bernardo Dr. San Diego, CA 92127 Tel: 858-524-2134 Fax:858-676-2228 jie@stbernard.com
Jie Liang <jie@stbernard.com> writes: > ####I expect to see something like: You're neglecting the effects of search path. \dt only shows what's visible in your search path. (IIRC, \dt *.* is the easiest way to see all tables regardless of search path.) > #### it seems if schema name is same as username then > #### it will shadow the objects in the public schema that have same name. That's what it's supposed to do, and yes this is documented, and yes you can change it if you don't like it. Read about search_path. regards, tom lane
Jie Liang wrote: > ####I expect to see something like: > List of relations > Schema | Name | Type | Owner > --------+------+-------+------- > public | foo | table | robot > t | foo | table | robot That's because schema t is not in your search path. By default, search path is: regression=# show search_path ; search_path -------------- $user,public (1 row) So you are not seeing the table in schema foo. If you do: regression=# create user robot; CREATE USER regression=# create schema t AUTHORIZATION robot; CREATE SCHEMA regression=# drop table foo; DROP TABLE regression=# create table foo(test text); CREATE TABLE regression=# create table t.foo(test text); CREATE TABLE regression=# \dt List of relations Schema | Name | Type | Owner --------+--------+-------+---------- public | foo | table | postgres public | table1 | table | postgres public | table2 | table | postgres (3 rows) regression=# set search_path to 't','public'; SET regression=# \dt List of relations Schema | Name | Type | Owner --------+--------+-------+---------- public | table1 | table | postgres public | table2 | table | postgres t | foo | table | postgres (3 rows) The $user in the default search path allows user robot to automatically find objects in schema robot first. You can change the default search path for the installation in postgresql.conf, or you can change in via ALTER DATABASE or ALTER USER to be effective in just one database or for one user respectively. HTH, Joe