X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id C8EFB11F6F42 for ; Sun, 2 Apr 2006 06:46:59 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 41717-09 for ; Sun, 2 Apr 2006 06:46:58 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from ra.sai.msu.su (ra.sai.msu.su [158.250.29.2]) by postgresql.org (Postfix) with ESMTP id EF9D911F6F28 for ; Sun, 2 Apr 2006 06:46:49 -0300 (ADT) Received: from ra (ra [158.250.29.2]) by ra.sai.msu.su (8.13.4/8.13.4) with ESMTP id k329kZxM002551; Sun, 2 Apr 2006 13:46:35 +0400 (MSD) Date: Sun, 2 Apr 2006 13:46:35 +0400 (MSD) From: Oleg Bartunov X-X-Sender: megera@ra.sai.msu.su To: Grega Bremec cc: andy.shellam@mailnetwork.co.uk, pgsql-admin@postgresql.org Subject: Re: Show tables query In-Reply-To: <442F9AA3.7060608@p0f.net> Message-ID: References: <20060402090706.6EB0911F6B01@postgresql.org> <442F9AA3.7060608@p0f.net> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.22 required=5 tests=[AWL=0.220] X-Spam-Score: 0.22 X-Spam-Level: X-Archive-Number: 200604/9 X-Sequence-Number: 21178 On Sun, 2 Apr 2006, Grega Bremec wrote: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: RIPEMD160 > > Andy Shellam wrote: > | Hi, > | > | Is there an SQL command supported by Postgres to return a list of > | tables in a database? > | > | For example on mySQL, you can connect to a database and issue the > | command SHOW TABLES to bring back a list of tables in that database. > | In PG this throws the error "unknown configuration parameter TABLES."> > > Hello, Andy. > > Not a command per se, but there are two ways you can obtain this > information, depending on where you're working. > > The first option is the backslash commands you can use from f.e. psql > (type \? in a psql prompt to see the full list), where \d will list all > sorts of database objects, \dt can be used specifically for tables. > > The other option which you can use from an SQL script is accessing the > system tables pg_class, pg_namespace and pg_tablespace in schema > pg_catalog, using a query similar to those used by the backslash commands: > > ~ template1=# SELECT c.relname AS table FROM pg_class c > ~ LEFT JOIN pg_namespace n ON n.oid = c.relnamespace > ~ WHERE n.nspname = 'public' > ~ AND c.relkind = 'r'; > > Substitute 'public' for whatever schema you're interested in or add > other schemas according to your preference. Also, take a look at system > view pg_tables (\d pg_tables). > > Also, take a look at the archives, Elein once posted a nice set of views > ~ and statements you can use for such purposes. psql -E will show you all queries that internal commands generate > > Hope this helped, > - -- > ~ Grega Bremec > ~ gregab at p0f dot net > -----BEGIN PGP SIGNATURE----- > Version: GnuPG v1.4.0 (GNU/Linux) > > iD8DBQFEL5qjfu4IwuB3+XoRA7CDAKCBFI7749wtFfsf4GXm3JcPQBU81gCffZMT > GbeTZzo0T3RJBwvwlK61O9c= > =NhhS > -----END PGP SIGNATURE----- > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org > Regards, Oleg _____________________________________________________________ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83