BUG #14578: Tables with same name in different schema are not shown with \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]https://www.postgresql.org/docs/9.6/static/app-psql.html.
"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,
/messages/by-id/20170208113300.1411.83851@wrigleys.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