Thread: [BUGS] BUG #14578: Tables with same name in different schema are not shownwith \dt

The following bug has been logged on the website:

Bug reference:      14578
Logged by:          François Beausoleil
Email address:      francois@teksol.info
PostgreSQL version: 9.6.2
Operating system:   Mac OS 10.11.6
Description:

In my schema, I happen to have two tables in different schemas with the same
name. PG supports that no problem, but when I listed the tables using \dt
and the search path set to both schemas, I expected to see the two tables.
Sadly, that wasn't the case.

https://gist.github.com/francois/2db220bd197492d02e0b60224a7576ac

-- repro.sql
create schema mybank;
create table public.a();
create table mybank.a();
set search_path to public;
\dt
set search_path to mybank;
\dt
set search_path to mybank, public;
\dt
set search_path to public, mybank;
\dt


-- run
$ psql --no-psqlrc --quiet repro -f b.sql
        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 public | a    | table | francois
(1 row)

        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 mybank | a    | table | francois
(1 row)

        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 mybank | a    | table | francois
(1 row)

        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 public | a    | table | francois
(1 row)


--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

On 04-03-2017 11:51, francois@teksol.info wrote:
> In my schema, I happen to have two tables in different schemas with the same
> name. PG supports that no problem, but when I listed the tables using \dt
> and the search path set to both schemas, I expected to see the two tables.
> Sadly, that wasn't the case.
> 
It is not a bug. It is a documented behavior [1].

"An object is said to be visible if its containing schema is in the
search path and no object of the same kind and name appears earlier in
the search path."


[1] https://www.postgresql.org/docs/9.6/static/app-psql.html


-- 
   Euler Taveira                   Timbira - http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Euler Taveira <euler@timbira.com.br> writes:
> On 04-03-2017 11:51, francois@teksol.info wrote:
>> In my schema, I happen to have two tables in different schemas with the same
>> name. PG supports that no problem, but when I listed the tables using \dt
>> and the search path set to both schemas, I expected to see the two tables.
>> Sadly, that wasn't the case.

> It is not a bug. It is a documented behavior [1].

Indeed.  See the last discussion of this,
https://www.postgresql.org/message-id/flat/20170208113300.1411.83851%40wrigleys.postgresql.org

An approximation to what you want can be had with "\dt *.tablename"
or perhaps "\dt *.*".

            regards, tom lane


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Hello François,

> name. PG supports that no problem, but when I listed the tables using \dt
> and the search path set to both schemas, I expected to see the two tables.

From the source code this this is a somehow debatable but voluntary 
feature. The query explicitely checks whether the table is visible:

   ... AND pg_catalog.pg_table_is_visible(c.oid)

That is whether it is ahead in the path, so the second one is masked, as 
doc says:

"""
Whenever the pattern parameter is omitted completely, the \d commands 
display all objects that are visible in the current schema search path — 
this is equivalent to using * as the pattern. (An object is said to be 
visible if its containing schema is in the search path and no object of 
the same kind and name appears earlier in the search path. This is 
equivalent to the statement that the object can be referenced by name 
without explicit schema qualification.) To see all objects in the database 
regardless of visibility, use *.* as the pattern.
"""

Now ISTM that "psql" documentation could be clearer: the explanation is 
hidden within the "pattern" description...

Note that the visibility filtering cannot be removed easily, because then 
the ordering (currently schema/name) would have to be fixed as well, and 
people would be surprised somehow by the resulting display...

-- 
Fabien.
-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

> Le 4 mars 2017 à 10:10, Euler Taveira <euler@timbira.com.br> a écrit :
>
> On 04-03-2017 11:51, francois@teksol.info wrote:
>> In my schema, I happen to have two tables in different schemas with the same
>> name. PG supports that no problem, but when I listed the tables using \dt
>> and the search path set to both schemas, I expected to see the two tables.
>> Sadly, that wasn't the case.
>>
> It is not a bug. It is a documented behavior [1].
>
> "An object is said to be visible if its containing schema is in the
> search path and no object of the same kind and name appears earlier in
> the search path."
>
>
> [1] https://www.postgresql.org/docs/9.6/static/app-psql.html

Oh, OK. That was surprising. I don't usually have tables with the same name in different schemas.

Thanks for the quick replies!
François



--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs