Обсуждение: [ADMIN] behavior of \dt and schemas
Hi all,
I've come across some rather non-intuitive behavior with \dt output. Say I have schema A and schema B, both with an identical mytable in them. If I set the search_path to A; and type \dt, I seeList of relations
-------------------------------------------------------
a | mytable | table | postgres
So far so good, and I get a similar output If I just set the search_path to B, only now it properly shows the schema as b. However, if I set the search_path to a, b; and enter \dt, I only see the tables from the first schema "a", like the above. and not the second schema. I would have expected:
List of relations
Schema | Name | Type | Owner
-------------------------------------------------------
a | mytable | table | postgres b | mytable | table | postgres
So, to me this is somewhat non-intuitive behavior, but maybe I'm all wet here. Shouldn't \dt report all the tables it can see with the search_path set to some value? And btw, this is was the behavior on 9.4.10, so if it's changed in more recent versions, I haven't tested there yet.
Flame suit on,
Jay
John, * John Scalia (jayknowsunix@gmail.com) wrote: > So far so good, and I get a similar output If I just set the search_path to > B, only now it properly shows the schema as b. However, if I set the > search_path to a, b; and enter \dt, I only see the tables from the first > schema "a", like the above. and not the second schema. I would have > expected: That's intentional as an unqualified object reference would pick up the object from the schema which shows up first in your search_path. > So, to me this is somewhat non-intuitive behavior, but maybe I'm all wet > here. Shouldn't \dt report all the tables it can see with the search_path > set to some value? And btw, this is was the behavior on 9.4.10, so if it's > changed in more recent versions, I haven't tested there yet. It's not changed. Thanks! Stephen
Вложения
On Friday, January 20, 2017, John Scalia <jayknowsunix@gmail.com> wrote:
So, to me this is somewhat non-intuitive behavior, but maybe I'm all wet here. Shouldn't \dt report all the tables it can see with the search_path set to some value? And btw, this is was the behavior on 9.4.10, so if it's changed in more recent versions, I haven't tested there yet.
It shows the definition of the table you would be referencing if you used that name in a query. This seems like a useful behavior.
David J.
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Friday, January 20, 2017, John Scalia <jayknowsunix@gmail.com> wrote:
>> So, to me this is somewhat non-intuitive behavior, but maybe I'm all wet
>> here. Shouldn't \dt report all the tables it can see with the search_path
>> set to some value? And btw, this is was the behavior on 9.4.10, so if it's
>> changed in more recent versions, I haven't tested there yet.
> It shows the definition of the table you would be referencing if you used
> that name in a query. This seems like a useful behavior.
Right --- according to our normal terminology, b.mytable is *not* visible,
because it is masked by a.mytable being ahead of it in the search path.
You'd have to write a qualified name to get at b.mytable.
You can write, eg, "\dt *.mytable" or "\dt *.*" if you would like it to
show tables that are not visible according to this rule. Without a
dot in the pattern, \dt shows only visible tables, ie only the ones
you could name without putting a dot in the name.
regards, tom lane
Understood, but I would have suspected that the search_path value would have been expanded to report all the tables visible under each schema, and not mask any. It just surprised me a little.
On Fri, Jan 20, 2017 at 10:37 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Friday, January 20, 2017, John Scalia <jayknowsunix@gmail.com> wrote:
>> So, to me this is somewhat non-intuitive behavior, but maybe I'm all wet
>> here. Shouldn't \dt report all the tables it can see with the search_path
>> set to some value? And btw, this is was the behavior on 9.4.10, so if it's
>> changed in more recent versions, I haven't tested there yet.
> It shows the definition of the table you would be referencing if you used
> that name in a query. This seems like a useful behavior.
Right --- according to our normal terminology, b.mytable is *not* visible,
because it is masked by a.mytable being ahead of it in the search path.
You'd have to write a qualified name to get at b.mytable.
You can write, eg, "\dt *.mytable" or "\dt *.*" if you would like it to
show tables that are not visible according to this rule. Without a
dot in the pattern, \dt shows only visible tables, ie only the ones
you could name without putting a dot in the name.
regards, tom lane
Especially as I just tested setting the search_path to both schemas and then created another new and uniquely named table under the second schema so this time \dt showed just the duplicated tables from the first schema, but also showed the uniquely named table from the second. That latter part would have been my expected behavior. I expected you'd all disagree with me, but it still seems a bit non-intuitive to me.
On Fri, Jan 20, 2017 at 11:09 AM, John Scalia <jayknowsunix@gmail.com> wrote:
Understood, but I would have suspected that the search_path value would have been expanded to report all the tables visible under each schema, and not mask any. It just surprised me a little.On Fri, Jan 20, 2017 at 10:37 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Friday, January 20, 2017, John Scalia <jayknowsunix@gmail.com> wrote:
>> So, to me this is somewhat non-intuitive behavior, but maybe I'm all wet
>> here. Shouldn't \dt report all the tables it can see with the search_path
>> set to some value? And btw, this is was the behavior on 9.4.10, so if it's
>> changed in more recent versions, I haven't tested there yet.
> It shows the definition of the table you would be referencing if you used
> that name in a query. This seems like a useful behavior.
Right --- according to our normal terminology, b.mytable is *not* visible,
because it is masked by a.mytable being ahead of it in the search path.
You'd have to write a qualified name to get at b.mytable.
You can write, eg, "\dt *.mytable" or "\dt *.*" if you would like it to
show tables that are not visible according to this rule. Without a
dot in the pattern, \dt shows only visible tables, ie only the ones
you could name without putting a dot in the name.
regards, tom lane