pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedbehavior of \dt and schemas
6+ messages / 4 participants
[nested] [flat]
* behavior of \dt and schemas
@ 2017-01-20 14:53 John Scalia <jayknowsunix@gmail.com>
2017-01-20 15:11 ` Re: behavior of \dt and schemas Stephen Frost <sfrost@snowman.net>
2017-01-20 15:11 ` Re: behavior of \dt and schemas David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 2 replies; 6+ messages in thread
From: John Scalia @ 2017-01-20 14:53 UTC (permalink / raw)
To: pgsql-admin
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 see
List of relations
Schema | Name | Type | Owner
-------------------------------------------------------
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: behavior of \dt and schemas
2017-01-20 14:53 behavior of \dt and schemas John Scalia <jayknowsunix@gmail.com>
@ 2017-01-20 15:11 ` Stephen Frost <sfrost@snowman.net>
1 sibling, 0 replies; 6+ messages in thread
From: Stephen Frost @ 2017-01-20 15:11 UTC (permalink / raw)
To: John Scalia <jayknowsunix@gmail.com>; +Cc: pgsql-admin
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
Attachments:
signature.asc (application/pgp-signature, 818B) download
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: behavior of \dt and schemas
2017-01-20 14:53 behavior of \dt and schemas John Scalia <jayknowsunix@gmail.com>
@ 2017-01-20 15:11 ` David G. Johnston <david.g.johnston@gmail.com>
2017-01-20 15:37 ` Re: behavior of \dt and schemas Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 6+ messages in thread
From: David G. Johnston @ 2017-01-20 15:11 UTC (permalink / raw)
To: John Scalia <jayknowsunix@gmail.com>; +Cc: pgsql-admin
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: behavior of \dt and schemas
2017-01-20 14:53 behavior of \dt and schemas John Scalia <jayknowsunix@gmail.com>
2017-01-20 15:11 ` Re: behavior of \dt and schemas David G. Johnston <david.g.johnston@gmail.com>
@ 2017-01-20 15:37 ` Tom Lane <tgl@sss.pgh.pa.us>
2017-01-20 16:09 ` Re: behavior of \dt and schemas John Scalia <jayknowsunix@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2017-01-20 15:37 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: John Scalia <jayknowsunix@gmail.com>; pgsql-admin
"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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: behavior of \dt and schemas
2017-01-20 14:53 behavior of \dt and schemas John Scalia <jayknowsunix@gmail.com>
2017-01-20 15:11 ` Re: behavior of \dt and schemas David G. Johnston <david.g.johnston@gmail.com>
2017-01-20 15:37 ` Re: behavior of \dt and schemas Tom Lane <tgl@sss.pgh.pa.us>
@ 2017-01-20 16:09 ` John Scalia <jayknowsunix@gmail.com>
2017-01-20 16:18 ` Re: behavior of \dt and schemas John Scalia <jayknowsunix@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: John Scalia @ 2017-01-20 16:09 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; pgsql-admin
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
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: behavior of \dt and schemas
2017-01-20 14:53 behavior of \dt and schemas John Scalia <jayknowsunix@gmail.com>
2017-01-20 15:11 ` Re: behavior of \dt and schemas David G. Johnston <david.g.johnston@gmail.com>
2017-01-20 15:37 ` Re: behavior of \dt and schemas Tom Lane <tgl@sss.pgh.pa.us>
2017-01-20 16:09 ` Re: behavior of \dt and schemas John Scalia <jayknowsunix@gmail.com>
@ 2017-01-20 16:18 ` John Scalia <jayknowsunix@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: John Scalia @ 2017-01-20 16:18 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; pgsql-admin
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
>>
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2017-01-20 16:18 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-01-20 14:53 behavior of \dt and schemas John Scalia <jayknowsunix@gmail.com>
2017-01-20 15:11 ` Stephen Frost <sfrost@snowman.net>
2017-01-20 15:11 ` David G. Johnston <david.g.johnston@gmail.com>
2017-01-20 15:37 ` Tom Lane <tgl@sss.pgh.pa.us>
2017-01-20 16:09 ` John Scalia <jayknowsunix@gmail.com>
2017-01-20 16:18 ` John Scalia <jayknowsunix@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox