describe working as intended?
So, I noticed that if I make a table in one schema and then a table with the
same name in another schema that describe only shows me one of them.
Demonstrating with temp table and regular table just for simplicity:
If I make a temp table t1 and a normal table t1 (it doesn't
matter which one I create first), describe only shows the temp table.
test=# create table t1();
CREATE TABLE
test=# \d
List of relations
Schema | Name | Type | Owner
--------+------+-------+-----------
public | t1 | table | mplageman
(1 row)
test=# create temp table t1();
CREATE TABLE
test=# \d
List of relations
Schema | Name | Type | Owner
-----------+------+-------+-----------
pg_temp_4 | t1 | table | mplageman
(1 row)
I'm not sure if this is the intended behavior or if it is a bug.
I looked briefly at the describe code and ran the query in
describeTableDetails
which it constructs at the beginning and this, of course, returns the
results I
would expect.
test=# select c.oid, n.nspname, c.relname from pg_catalog.pg_class c left
join
pg_catalog.pg_namespace n on n.oid = c.relnamespace where c.relname = 't1';
oid | nspname | relname
-------+-----------+---------
23609 | public | t1
23612 | pg_temp_4 | t1
(2 rows)
So, without much more digging, is the current behavior of describe intended?
I couldn't find an email thread discussing this with the search terms I
tried.
(I noticed it on master and checked 11 as well and got the same behavior.)
--
Melanie Plageman
Melanie Plageman <melanieplageman@gmail.com> writes:
So, I noticed that if I make a table in one schema and then a table with the
same name in another schema that describe only shows me one of them.
Yes, that's intended, psql's \d will only show you tables that are
visible in the search path, unless you give it a qualified pattern.
You can do something like "\d *.t1" if you want to see all the
instances of t1.
This is documented I believe ... ah yes, here:
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.
...
A pattern that contains a dot (.) is interpreted as a schema name
pattern followed by an object name pattern. For example, \dt
foo*.*bar* displays all tables whose table name includes bar that are
in schemas whose schema name starts with foo. When no dot appears,
then the pattern matches only objects that are visible in the current
schema search path. Again, a dot within double quotes loses its
special meaning and is matched literally.
regards, tom lane
Hello
No, this is not bug. This is expected beharior of search_path setting: https://www.postgresql.org/docs/current/runtime-config-client.html
Likewise, the current session's temporary-table schema, pg_temp_nnn, is always searched if it exists. It can be explicitly listed in the path by using the alias pg_temp. If it is not listed in the path then it is searched first
psql \d command checks current search_path (by pg_table_is_visible call). You can use \d *.t1 syntax to display tables with such name in all schemas.
regards, Sergei
On Sat, May 18, 2019 at 1:17 AM Sergei Kornilov <sk@zsrv.org> wrote:
Hello
No, this is not bug. This is expected beharior of search_path setting:
https://www.postgresql.org/docs/current/runtime-config-client.htmlLikewise, the current session's temporary-table schema, pg_temp_nnn, is
always searched if it exists. It can be explicitly listed in the path by
using the alias pg_temp. If it is not listed in the path then it is
searched firstpsql \d command checks current search_path (by pg_table_is_visible call).
You can use \d *.t1 syntax to display tables with such name in all schemas.regards, Sergei
Thanks! I suppose it would behoove me to check the documentation
before resorting to looking at the source code :)
--
Melanie Plageman