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 7576011F6AA1 for ; Sun, 2 Apr 2006 06:58:56 -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 44737-01 for ; Sun, 2 Apr 2006 06:58:55 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sydney.mailnetwork.co.uk (server88-208-192-113.live-servers.net [88.208.192.113]) by postgresql.org (Postfix) with ESMTP id EDD8811F6F0E for ; Sun, 2 Apr 2006 06:58:53 -0300 (ADT) Received: from andyshellam-gw.adsl.newnet.co.uk ([80.175.198.137]:1585 helo=acsdesk1) by sydney.mailnetwork.co.uk with esmtpa (Exim 4.60) (envelope-from ) id 1FPzMD-0001c3-Gy for pgsql-admin@postgresql.org; Sun, 02 Apr 2006 10:58:53 +0100 Reply-To: From: "Andy Shellam" To: Subject: FW: Show tables query Date: Sun, 2 Apr 2006 10:58:01 +0100 Organization: the mail network MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Mailer: Microsoft Office Outlook, Build 11.0.5510 Thread-Index: AcZWOJ7bJeuB59MHQci4CUrXu/sGSAAAwFQgAAARciA= X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 Message-Id: <20060402095853.EDD8811F6F0E@postgresql.org> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.951 required=5 tests=[AWL=0.024, MSGID_FROM_MTA_ID=0.927] X-Spam-Score: 0.951 X-Spam-Level: X-Archive-Number: 200604/10 X-Sequence-Number: 21179 Hi Grega, That's perfect thanks. I needed the SQL as it will be running in a web application, and I need to use the resulting list for processing within the application. Many thanks Andy -----Original Message----- From: Grega Bremec [mailto:gregab@p0f.net] Sent: Sunday, 02 April, 2006 10:34 am To: andy.shellam@mailnetwork.co.uk Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] Show tables query -----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. 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----- !DSPAM:14,442f9a9c35047994616568!