Thread: listing relations
Something interesting I've noticed. If I have a table by the same name in two different schemas, say public and foo, and my search path is set to 'public, foo', \d without an argument lists only the one in public. I see why from the SQL that \d generates, but just wondering why \d doesn't generate SQL to list both. -- Brandon
Brandon Metcalf <brandon@geronimoalloys.com> writes:
> Something interesting I've noticed. If I have a table by the same
> name in two different schemas, say public and foo, and my search path
> is set to 'public, foo', \d without an argument lists only the one in
> public.
That's intentional. It's designed to show the same table you'd get if
you did "select * from tabname". You can do "\d *.tabname" if you want
to see all tables named tabname regardless of schema.
regards, tom lane
t == tgl@sss.pgh.pa.us writes: t> Brandon Metcalf <brandon@geronimoalloys.com> writes: t> > Something interesting I've noticed. If I have a table by the same t> > name in two different schemas, say public and foo, and my search path t> > is set to 'public, foo', \d without an argument lists only the one in t> > public. t> That's intentional. It's designed to show the same table you'd get if t> you did "select * from tabname". You can do "\d *.tabname" if you want t> to see all tables named tabname regardless of schema. Yeah, I figured there was a reason for the SQL being crafted that way. Is there a "\" command to show all tables in the current search path? -- Brandon
On 11/06/2009 21:39, Brandon Metcalf wrote: > Is there a "\" command to show all tables in the current search path? \dt \? is your friend.... Ray. ------------------------------------------------------------------ Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland rod@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals ------------------------------------------------------------------
r == rod@iol.ie writes: r> On 11/06/2009 21:39, Brandon Metcalf wrote: r> > Is there a "\" command to show all tables in the current search path? r> \dt r> \? is your friend.... Nope. You didn't read the entire thread. If you do, you'll see why \dt isn't the answer. I'm well aware of \?. -- Brandon
Brandon Metcalf <brandon@geronimoalloys.com> writes:
> Is there a "\" command to show all tables in the current search path?
Even ones that are masked by earlier search_path entries? No.
You could craft some manual query on pg_class, no doubt.
regards, tom lane
t == tgl@sss.pgh.pa.us writes: t> Brandon Metcalf <brandon@geronimoalloys.com> writes: t> > Is there a "\" command to show all tables in the current search path? t> Even ones that are masked by earlier search_path entries? No. Correct. Just wondering if there was something undocumented :) t> You could craft some manual query on pg_class, no doubt. Indeed. -- Brandon
On Thu, 11 Jun 2009, Brandon Metcalf wrote: > Is there a "\" command to show all tables in the current search path? SELECT nspname,relname,relkind FROM pg_class c, pg_namespace n WHERE c.relnamespace = n.oid AND relkind='r' AND nspname !~ '^pg_toast' AND nspname = ANY(current_schemas(false)) ORDER BY nspname,relname; Change "false" to "true" if you want to include the stuff in pg_catalog too. Remove the filter on relkind if you want to see things besides just tables. There's a bunch of other system info functions you might find useful documented at http://www.postgresql.org/docs/8.3/static/functions-info.html as well. (The pg_toast filter is probably redundant here, I try to keep that in all these pg_class/pg_namespace join examples because it's handy for more normal queries) -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD