About psql \dt unable display same name table which have different schema

Started by 徐志宇徐almost 4 years ago3 messagesgeneral
Jump to latest
#1徐志宇徐
xuzhiyuster@gmail.com

Hello All

I experience a problem. The psql \dt command unable display
same name table which have different schema.

The table new. test10 unable display.
Owing to the public.test10 exist. The pg_type_is_visible display "F"

I don't know why this problem exist. Why unable display both table "
public.test10 、 new. test10 "

For example:

new01=# create table public.test10 (id int);
CREATE TABLE
new01=# create table new.test10(id int);
CREATE TABLE
new01=# create table new.test11(id int);
CREATE TABLE
new01=# show search_path ;
search_path
----------------------------
"$user", public, new2, new
(1 row)

new01=# \dt
List of relations
Schema | Name | Type | Owner
--------+--------+-------+----------
new | test01 | table | test01
new | test02 | table | postgres
new | test11 | table | postgres
public | tbl_a | table | postgres
public | tbl_c | table | postgres
public | test10 | table | postgres
(6 rows)
new01=# SELECT pg_type_is_visible('public.test10'::regtype);
pg_type_is_visible
--------------------
t
(1 row)

new01=# SELECT pg_type_is_visible('new.test10'::regtype);
pg_type_is_visible
--------------------
f
(1 row)

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: 徐志宇徐 (#1)
Re: About psql \dt unable display same name table which have different schema

=?UTF-8?B?5b6Q5b+X5a6H5b6Q?= <xuzhiyuster@gmail.com> writes:

I don't know why this problem exist. Why unable display both table "
public.test10 、 new. test10 "

That's behaving as designed: \dt will show you tables that are
accessible with an unqualified name, but new.test10 is hidden
behind public.test10, so it's not accessible except by
qualification.

You can do "\dt *.*" or "\dt new.*", etc, to see such tables.

See here for more info:

https://www.postgresql.org/docs/current/app-psql.html#APP-PSQL-PATTERNS

regards, tom lane

#3徐志宇徐
xuzhiyuster@gmail.com
In reply to: Tom Lane (#2)
Re: About psql \dt unable display same name table which have different schema

Hi Tom.

Thanks for your explanation. I got it.
I really appreciate it.

Jack Xu.

Tom Lane <tgl@sss.pgh.pa.us> 于2022年5月25日周三 00:16写道:

Show quoted text

=?UTF-8?B?5b6Q5b+X5a6H5b6Q?= <xuzhiyuster@gmail.com> writes:

I don't know why this problem exist. Why unable display both table "
public.test10 、 new. test10 "

That's behaving as designed: \dt will show you tables that are
accessible with an unqualified name, but new.test10 is hidden
behind public.test10, so it's not accessible except by
qualification.

You can do "\dt *.*" or "\dt new.*", etc, to see such tables.

See here for more info:

https://www.postgresql.org/docs/current/app-psql.html#APP-PSQL-PATTERNS

regards, tom lane