why schema name is same as username behaves different then others

From: Jie Liang <jie(at)stbernard(dot)com>
To: 'Bruce Momjian' <pgman(at)candle(dot)pha(dot)pa(dot)us>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: why schema name is same as username behaves different then others
Date: 2002-12-05 22:49:02
Message-ID: E7E213858379814A9AE48CA6754F5ECB1E192A@mail01.stbernard.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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(at)stbernard(dot)com

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Tom Lane 2002-12-05 22:57:50 Re: why schema name is same as username behaves different then others
Previous Message Jie Liang 2002-12-05 22:40:03 Re: list schema