From: Grega Bremec <gregab@p0f.net>
To: andy.shellam@mailnetwork.co.uk
Cc: pgsql-admin@postgresql.org
Subject: Re: Show tables query
Date: Sun, 02 Apr 2006 11:34:27 +0200
Message-ID: <442F9AA3.7060608@p0f.net> (raw)
In-Reply-To: <20060402090706.6EB0911F6B01@postgresql.org>
References: <20060402090706.6EB0911F6B01@postgresql.org>
-----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-----
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: gregab@p0f.net, andy.shellam@mailnetwork.co.uk
Subject: Re: Show tables query
In-Reply-To: <442F9AA3.7060608@p0f.net>
References: <20060402090706.6EB0911F6B01@postgresql.org> <442F9AA3.7060608@p0f.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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