From andy.shellam@mailnetwork.co.uk Sun Apr 2 09:07:09 2006 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 957D911F6C70 for ; Sun, 2 Apr 2006 06:07:09 -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 36099-03 for ; Sun, 2 Apr 2006 06:07:07 -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 6EB0911F6B01 for ; Sun, 2 Apr 2006 06:07:06 -0300 (ADT) Received: from andyshellam-gw.adsl.newnet.co.uk ([80.175.198.137]:1532 helo=acsdesk1) by sydney.mailnetwork.co.uk with esmtpa (Exim 4.60) (envelope-from ) id 1FPyY5-0001Xi-4e for pgsql-admin@postgresql.org; Sun, 02 Apr 2006 10:07:05 +0100 Reply-To: From: "Andy Shellam" To: Subject: Show tables query Date: Sun, 2 Apr 2006 10:06:14 +0100 Organization: the mail network MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0004_01C6563D.13C1A750" X-Mailer: Microsoft Office Outlook, Build 11.0.5510 Thread-Index: AcZWNLCvnjlSK5dQTNSJdGl7OVfDvg== X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 Message-Id: <20060402090706.6EB0911F6B01@postgresql.org> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.939 required=5 tests=[AWL=0.011, HTML_MESSAGE=0.001, MSGID_FROM_MTA_ID=0.927] X-Spam-Score: 0.939 X-Spam-Level: X-Archive-Number: 200604/7 X-Sequence-Number: 21176 This is a multi-part message in MIME format. ------=_NextPart_000_0004_01C6563D.13C1A750 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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." Any ideas? Thanks Andy ------=_NextPart_000_0004_01C6563D.13C1A750 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable
Hi,
 
Is = there an SQL=20 command supported by Postgres to return a list of tables in a=20 database?
 
For = example on=20 mySQL, you can connect to a database and issue the command SHOW TABLES = to bring=20 back a list of tables in that database.
In PG = this throws=20 the error "unknown configuration parameter TABLES."
 
Any=20 ideas?
 
Thanks
 
Andy
------=_NextPart_000_0004_01C6563D.13C1A750-- From gregab@p0f.net Sun Apr 2 09:34:16 2006 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 1E13C11F6D47 for ; Sun, 2 Apr 2006 06:34:16 -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 40825-01 for ; Sun, 2 Apr 2006 06:34:15 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from p0f.net (p0f.net [193.77.154.190]) by postgresql.org (Postfix) with ESMTP id 8DC4911F6D43 for ; Sun, 2 Apr 2006 06:34:12 -0300 (ADT) Received: from [192.168.13.30] (BSN-210-248-159.dial-up.dsl.siol.net [195.210.248.159]) (authenticated bits=0) by p0f.net (8.13.3/8.13.3) with ESMTP id k328sisK032136 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO); Sun, 2 Apr 2006 10:54:48 +0200 Message-ID: <442F9AA3.7060608@p0f.net> Date: Sun, 02 Apr 2006 11:34:27 +0200 From: Grega Bremec Organization: P0F User-Agent: Mozilla Thunderbird 1.0 (X11/20041207) X-Accept-Language: en-us, en MIME-Version: 1.0 To: andy.shellam@mailnetwork.co.uk CC: pgsql-admin@postgresql.org Subject: Re: Show tables query References: <20060402090706.6EB0911F6B01@postgresql.org> In-Reply-To: <20060402090706.6EB0911F6B01@postgresql.org> X-Enigmail-Version: 0.93.0.0 OpenPGP: id=E077F97A Content-Type: text/plain; charset=ISO-8859-2; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=1.405 required=5 tests=[AWL=-0.308, RCVD_IN_NJABL_DUL=1.713] X-Spam-Score: 1.405 X-Spam-Level: * X-Archive-Number: 200604/8 X-Sequence-Number: 21177 -----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----- From oleg@sai.msu.su Sun Apr 2 09:46:59 2006 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 From andy.shellam@mailnetwork.co.uk Sun Apr 2 09:58:56 2006 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! From cbbrowne@acm.org Sun Apr 2 13:17:23 2006 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 1BA8411F6AA9 for ; Sun, 2 Apr 2006 10:17:23 -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 68882-09 for ; Sun, 2 Apr 2006 10:17:22 -0300 (ADT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by postgresql.org (Postfix) with ESMTP id 0C58311F6D8D for ; Sun, 2 Apr 2006 10:17:21 -0300 (ADT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.13.1/8.13.1) with ESMTP id k32DHEuf078107 for ; Sun, 2 Apr 2006 10:17:15 -0300 (ADT) (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.13.1/8.13.1/Submit) id k32D32C0075764 for pgsql-admin@postgresql.org; Sun, 2 Apr 2006 10:03:02 -0300 (ADT) (envelope-from news) From: Christopher Browne X-Newsgroups: pgsql.admin Subject: Re: Show tables query Date: Sun, 02 Apr 2006 08:48:37 -0400 Organization: cbbrowne Computing Inc Lines: 19 Message-ID: <878xqo9m8q.fsf@wolfe.cbbrowne.com> References: <20060402090706.6EB0911F6B01@postgresql.org> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Complaints-To: usenet@news.hub.org X-message-flag: Outlook is rather hackable, isn't it? X-Home-Page: http://www.cbbrowne.com/info/ X-Affero: http://svcs.affero.net/rm.php?r=cbbrowne User-Agent: Gnus/5.1007 (Gnus v5.10.7) XEmacs/21.4.19 (linux) Cancel-Lock: sha1:Ey6PD1J+XGnUs4HrBZ8mNPHuj/Y= To: pgsql-admin@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=-1.246 required=5 tests=[ALL_TRUSTED=-1.44, AWL=0.194] X-Spam-Score: -1.246 X-Spam-Level: X-Archive-Number: 200604/11 X-Sequence-Number: 21180 After takin a swig o' Arrakan spice grog, andy.shellam@mailnetwork.co.uk ("Andy Shellam") belched out: > Is there an SQL command supported by Postgres to return a list of tables in a database? Yes, it's called SELECT. There is a standard schema called INFORMATION_SCHEMA, which contains a variety of relevant views. Notably, you could request: SELECT * FROM INFORMATION_SCHEMA.TABLES; That has the merit of actually conforming to SQL standards... -- output = reverse("moc.liamg" "@" "enworbbc") http://cbbrowne.com/info/ They have finally found the most ultimately useless thing on the web... Found at the Victoria's Secret website: "The online shop: Text Only Listing" From mallah.rajesh@gmail.com Sun Apr 2 13:47:14 2006 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 7D7D911F78EC for ; Sun, 2 Apr 2006 10:47:14 -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 74370-09 for ; Sun, 2 Apr 2006 10:47:14 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from nproxy.gmail.com (nproxy.gmail.com [64.233.182.188]) by postgresql.org (Postfix) with ESMTP id 4555F11F76C0 for ; Sun, 2 Apr 2006 10:47:10 -0300 (ADT) Received: by nproxy.gmail.com with SMTP id l24so784009nfc for ; Sun, 02 Apr 2006 06:47:10 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=RUQz/GK1z0eAMSdL+/vzgLngo+uYyGOieCQIYCQCwa7dZ7jlhj1D+qP+y17CBAws51Twkag4Jn5l0Yz6mScgUv0F+dsB3EgbYva2enYMNtkRP5uCG3tIiIO5OMX3NBwvjj2SodzPVxnlwHtfdMma1fw5qgnSWsNr1/H8mn0ogmM= Received: by 10.48.209.14 with SMTP id h14mr1609236nfg; Sun, 02 Apr 2006 06:47:10 -0700 (PDT) Received: by 10.49.26.20 with HTTP; Sun, 2 Apr 2006 06:47:10 -0700 (PDT) Message-ID: Date: Sun, 2 Apr 2006 19:17:10 +0530 From: "Rajesh Kumar Mallah" To: "Christopher Browne" Subject: Re: Show tables query Cc: pgsql-admin@postgresql.org In-Reply-To: <878xqo9m8q.fsf@wolfe.cbbrowne.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_23669_16198110.1143985630652" References: <20060402090706.6EB0911F6B01@postgresql.org> <878xqo9m8q.fsf@wolfe.cbbrowne.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.516 required=5 tests=[AWL=0.515, HTML_MESSAGE=0.001] X-Spam-Score: 0.516 X-Spam-Level: X-Archive-Number: 200604/12 X-Sequence-Number: 21181 ------=_Part_23669_16198110.1143985630652 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline On 4/2/06, Christopher Browne wrote: > > After takin a swig o' Arrakan spice grog, andy.shellam@mailnetwork.co.uk(= "Andy Shellam") belched out: > > Is there an SQL command supported by Postgres to return a list of table= s > in a database? > > Yes, it's called SELECT. > > There is a standard schema called INFORMATION_SCHEMA, which contains a > variety of relevant views. > > Notably, you could request: > > SELECT * FROM INFORMATION_SCHEMA.TABLES where table_type=3D'BASE TABLE' ; if you need tables only otherwise it returns the Views also. That has the merit of actually conforming to SQL standards... > -- > output =3D reverse("moc.liamg" "@" "enworbbc") > http://cbbrowne.com/info/ > They have finally found the most ultimately useless thing on the web... > Found at the Victoria's Secret website: > "The online shop: Text Only Listing" > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org > ------=_Part_23669_16198110.1143985630652 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline

On 4/2/06, Christopher Browne <cbbro= wne@acm.org> wrote:
After takin a swig o' Arrakan spice grog, andy.shellam@mailnetwork.co.uk ("Andy Shellam&quo= t;) belched out:
> Is there an SQL command supported by Postgres to r= eturn a list of tables in a database?

Yes, it's called SELECT.

There is a standard schema called I= NFORMATION_SCHEMA, which contains a
variety of relevant views.

No= tably, you could request:

SELECT * FROM INFORMATION_SCHEMA.TABLES


where table_type=3D'BASE TABLE'
 

;=

if you need tables only otherwise it returns the Views also.

That ha= s the merit of actually conforming to SQL standards...
--
output =3D = reverse(" moc.liamg" "@" "enworbbc")
http://cbbrowne.com/info/
They have finally found = the most ultimately useless thing on the web...
Found at the Victoria's = Secret website:
   "The online shop: Text Only Listing"

----= -----------------------(end of broadcast)---------------------------
TIP= 4: Have you searched our list archives?

    &nb= sp;          http://archives.postgresql.org

------=_Part_23669_16198110.1143985630652-- From andy.shellam@mailnetwork.co.uk Sun Apr 2 14:53:28 2006 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 CBA4411F6AE4 for ; Sun, 2 Apr 2006 11:53:28 -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 82415-09 for ; Sun, 2 Apr 2006 11:53:29 -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 D69E411F6AFD for ; Sun, 2 Apr 2006 11:53:26 -0300 (ADT) Received: from andyshellam-gw.adsl.newnet.co.uk ([80.175.198.137]:1812 helo=acsdesk1) by sydney.mailnetwork.co.uk with esmtpa (Exim 4.60) (envelope-from ) id 1FQ3xG-0001nk-VA; Sun, 02 Apr 2006 15:53:27 +0100 Reply-To: From: "Andy Shellam" To: "'Christopher Browne'" Cc: Subject: Re: Show tables query Date: Sun, 2 Apr 2006 15:53:11 +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 In-Reply-To: <878xqo9m8q.fsf@wolfe.cbbrowne.com> Thread-Index: AcZWV+DICpPDesZHTk2HDXCmRIjxqAADK4kg X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 Message-Id: <20060402145326.D69E411F6AFD@postgresql.org> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.961 required=5 tests=[AWL=0.033, MSGID_FROM_MTA_ID=0.927] X-Spam-Score: 0.961 X-Spam-Level: X-Archive-Number: 200604/14 X-Sequence-Number: 21183 > After takin a swig o' Arrakan spice grog, andy.shellam@mailnetwork.co.uk ("Andy Shellam") belched out: >> Is there an SQL command supported by Postgres to return a list of tables in a database? Sorry, did I say something wrong? I thought it was a perfectly valid question actually. The application in mind is going to be run exclusively on Postgres, so I'm not overly fussed over standards - I just wanted a quick win, of which Grega's SQL gave it me perfectly - tables only, nothing else included. From mallah.rajesh@gmail.com Sun Apr 2 15:32:13 2006 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 A57DD11F796F for ; Sun, 2 Apr 2006 12:32:13 -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 88551-05 for ; Sun, 2 Apr 2006 12:32:11 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from nproxy.gmail.com (nproxy.gmail.com [64.233.182.187]) by postgresql.org (Postfix) with ESMTP id D6DDF11F6AF5 for ; Sun, 2 Apr 2006 12:32:10 -0300 (ADT) Received: by nproxy.gmail.com with SMTP id l24so792318nfc for ; Sun, 02 Apr 2006 08:32:10 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=FtaFMaz966HjSXUU91I005AYGWgKz+LQERTgeWwiAXHNWvLyo5H1B3BdDCS8PbXA4tXGebv8yMKazvWfC+AWI67MbPc/ifzLGmHtkhDPz0xiY/7NcyAkpAnWL4E1bDHhGZZ9uoPi/RvCspVFdaG6dvuldrbwWuBRvi3FYVVf/FY= Received: by 10.49.80.4 with SMTP id h4mr1649743nfl; Sun, 02 Apr 2006 08:32:09 -0700 (PDT) Received: by 10.49.26.20 with HTTP; Sun, 2 Apr 2006 08:32:09 -0700 (PDT) Message-ID: Date: Sun, 2 Apr 2006 21:02:09 +0530 From: "Rajesh Kumar Mallah" To: andy.shellam@mailnetwork.co.uk Subject: Re: Show tables query Cc: "Christopher Browne" , pgsql-admin@postgresql.org In-Reply-To: <20060402145326.D69E411F6AFD@postgresql.org> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline References: <878xqo9m8q.fsf@wolfe.cbbrowne.com> <20060402145326.D69E411F6AFD@postgresql.org> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=1.148 required=5 tests=[AWL=-0.184, RCVD_IN_BL_SPAMCOP_NET=1.332] X-Spam-Score: 1.148 X-Spam-Level: * X-Archive-Number: 200604/15 X-Sequence-Number: 21184 On 4/2/06, Andy Shellam wrote: > > After takin a swig o' Arrakan spice grog, andy.shellam@mailnetwork.co.u= k > ("Andy Shellam") belched out: > >> Is there an SQL command supported by Postgres to return a list of tabl= es > in a database? > > Sorry, did I say something wrong? I thought it was a perfectly valid > question actually. The application in mind is going to be run exclusivel= y > on Postgres, > > so I'm not overly fussed over standards - I just wanted a quick > win, of which Grega's SQL gave it me perfectly - tables only, nothing els= e > included. the information_schema approach is still better than querying the system catalogs. The system catalogs are internal to postgresql what if future versions of postgresql change the sys catalogs dramaticall= y ? (your app breaks!) information_schema is the standard which are more likely to behave the same in all versions of pgsql becoz they are(currently) views on the sys catalogs. Regds Rajesh Kumar Mallah. > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org > From andy.shellam@mailnetwork.co.uk Sun Apr 2 15:41:00 2006 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 BB28F11F6258 for ; Sun, 2 Apr 2006 12:41:00 -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 91060-03 for ; Sun, 2 Apr 2006 12:40:58 -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 453A411F602F for ; Sun, 2 Apr 2006 12:40:57 -0300 (ADT) Received: from andyshellam-gw.adsl.newnet.co.uk ([80.175.198.137]:1864 helo=acsdesk1) by sydney.mailnetwork.co.uk with esmtpa (Exim 4.60) (envelope-from ) id 1FQ4hE-0001qj-R2; Sun, 02 Apr 2006 16:40:57 +0100 Reply-To: From: "Andy Shellam" To: "'Rajesh Kumar Mallah'" Cc: Subject: Re: Show tables query Date: Sun, 2 Apr 2006 16:40:40 +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 In-Reply-To: Thread-Index: AcZWar1a89wlcR8iSKixAHR9v6IELQAAH94g X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 Message-Id: <20060402154057.453A411F602F@postgresql.org> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.968 required=5 tests=[AWL=0.041, MSGID_FROM_MTA_ID=0.927] X-Spam-Score: 0.968 X-Spam-Level: X-Archive-Number: 200604/16 X-Sequence-Number: 21185 Thanks Rajesh, That's always a risk anyway with anything - hence where upgrade testing comes in ;-) I'll probably go this way if I do indeed have this need still - it was only a preliminary thought process, I just thought I'd ask the question. Thanks Andy -----Original Message----- From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Rajesh Kumar Mallah Sent: Sunday, 02 April, 2006 4:32 pm To: andy.shellam@mailnetwork.co.uk Cc: Christopher Browne; pgsql-admin@postgresql.org Subject: Re: [ADMIN] Show tables query On 4/2/06, Andy Shellam wrote: > > After takin a swig o' Arrakan spice grog, andy.shellam@mailnetwork.co.uk > ("Andy Shellam") belched out: > >> Is there an SQL command supported by Postgres to return a list of tables > in a database? > > Sorry, did I say something wrong? I thought it was a perfectly valid > question actually. The application in mind is going to be run exclusively > on Postgres, > > so I'm not overly fussed over standards - I just wanted a quick > win, of which Grega's SQL gave it me perfectly - tables only, nothing else > included. the information_schema approach is still better than querying the system catalogs. The system catalogs are internal to postgresql what if future versions of postgresql change the sys catalogs dramatically ? (your app breaks!) information_schema is the standard which are more likely to behave the same in all versions of pgsql becoz they are(currently) views on the sys catalogs. Regds Rajesh Kumar Mallah. > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org > ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend !DSPAM:14,442feeb335041315618668! From tgl@sss.pgh.pa.us Sun Apr 2 16:46:50 2006 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 B653A11F6966 for ; Sun, 2 Apr 2006 13:46:50 -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 00970-01 for ; Sun, 2 Apr 2006 13:46:49 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 9166C11F66DD for ; Sun, 2 Apr 2006 13:46:48 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id k32GkhJK023724; Sun, 2 Apr 2006 12:46:43 -0400 (EDT) To: "Rajesh Kumar Mallah" cc: andy.shellam@mailnetwork.co.uk, "Christopher Browne" , pgsql-admin@postgresql.org Subject: Re: Show tables query In-reply-to: References: <878xqo9m8q.fsf@wolfe.cbbrowne.com> <20060402145326.D69E411F6AFD@postgresql.org> Comments: In-reply-to "Rajesh Kumar Mallah" message dated "Sun, 02 Apr 2006 21:02:09 +0530" Date: Sun, 02 Apr 2006 12:46:43 -0400 Message-ID: <23723.1143996403@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.114 required=5 tests=[AWL=0.114] X-Spam-Score: 0.114 X-Spam-Level: X-Archive-Number: 200604/18 X-Sequence-Number: 21187 "Rajesh Kumar Mallah" writes: > information_schema is the standard which are more likely to behave > the same in all versions of pgsql becoz they are(currently) views on > the sys catalogs. Unfortunately, the SQL committee did tremendous damage to that argument by changing the definitions of some of those views in SQL2003 :-( I'd still agree that the information_schema is less likely to change than the underlying catalogs, but it's not an ironclad guarantee that your app won't break. (We haven't caught up to the SQL2003 behavior yet, but I believe Peter Eisentraut is working on it for PG 8.2.) regards, tom lane From peter_e@gmx.net Sun Apr 2 17:38:27 2006 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 4B03711F7DE4 for ; Sun, 2 Apr 2006 14:38:27 -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 08036-01 for ; Sun, 2 Apr 2006 14:38:25 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from mail.gmx.net (mail.gmx.net [213.165.64.20]) by postgresql.org (Postfix) with SMTP id 80D8611F7DBE for ; Sun, 2 Apr 2006 14:38:23 -0300 (ADT) Received: (qmail invoked by alias); 02 Apr 2006 17:38:22 -0000 Received: from dslb-084-063-054-068.pools.arcor-ip.net (EHLO colt.pezone.net) [84.63.54.68] by mail.gmx.net (mp042) with SMTP; 02 Apr 2006 19:38:22 +0200 X-Authenticated: #495269 From: Peter Eisentraut To: pgsql-admin@postgresql.org Subject: Re: Show tables query Date: Sun, 2 Apr 2006 19:38:19 +0200 User-Agent: KMail/1.8.3 Cc: Tom Lane , "Rajesh Kumar Mallah" , andy.shellam@mailnetwork.co.uk, "Christopher Browne" References: <878xqo9m8q.fsf@wolfe.cbbrowne.com> <23723.1143996403@sss.pgh.pa.us> In-Reply-To: <23723.1143996403@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200604021938.20644.peter_e@gmx.net> X-Y-GMX-Trusted: 0 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.133 required=5 tests=[AWL=0.133] X-Spam-Score: 0.133 X-Spam-Level: X-Archive-Number: 200604/21 X-Sequence-Number: 21190 Tom Lane wrote: > Unfortunately, the SQL committee did tremendous damage to that > argument by changing the definitions of some of those views in > SQL2003 :-( The only changes to existing views were one renamed column and two or three cases with relaxed permission checks so that more objects are now shown. I do not expect any application to break. > (We haven't caught up to the SQL2003 behavior yet, but I believe > Peter Eisentraut is working on it for PG 8.2.) I'm committing it now. Thanks for reminding me -- I had almost forgotten about that patch. :) -- Peter Eisentraut http://developer.postgresql.org/~petere/ From tgl@sss.pgh.pa.us Sun Apr 2 22:28:52 2006 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 15C9511F65C5 for ; Sun, 2 Apr 2006 19:28:52 -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 74491-01 for ; Sun, 2 Apr 2006 19:28:51 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id EC25711F6029 for ; Sun, 2 Apr 2006 19:28:49 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id k32MSkDl025460; Sun, 2 Apr 2006 18:28:46 -0400 (EDT) To: Peter Eisentraut cc: pgsql-admin@postgresql.org, "Rajesh Kumar Mallah" , andy.shellam@mailnetwork.co.uk, "Christopher Browne" Subject: Re: Show tables query In-reply-to: <200604021938.20644.peter_e@gmx.net> References: <878xqo9m8q.fsf@wolfe.cbbrowne.com> <23723.1143996403@sss.pgh.pa.us> <200604021938.20644.peter_e@gmx.net> Comments: In-reply-to Peter Eisentraut message dated "Sun, 02 Apr 2006 19:38:19 +0200" Date: Sun, 02 Apr 2006 18:28:46 -0400 Message-ID: <25459.1144016926@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.114 required=5 tests=[AWL=0.114] X-Spam-Score: 0.114 X-Spam-Level: X-Archive-Number: 200604/23 X-Sequence-Number: 21192 Peter Eisentraut writes: > Tom Lane wrote: >> Unfortunately, the SQL committee did tremendous damage to that >> argument by changing the definitions of some of those views in >> SQL2003 :-( > The only changes to existing views were one renamed column and two or > three cases with relaxed permission checks so that more objects are now > shown. I do not expect any application to break. I'm sure the committee thinks the same, and they may even be right. I'm just pointing out that they've lost the moral high ground of "this API will never change". regards, tom lane