pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedShow tables query
12+ messages / 7 participants
[nested] [flat]
* Show tables query
@ 2006-04-02 09:06 Andy Shellam <andy.shellam@mailnetwork.co.uk>
2006-04-02 09:34 ` Re: Show tables query Grega Bremec <gregab@p0f.net>
2006-04-02 12:48 ` Re: Show tables query Christopher Browne <cbbrowne@acm.org>
0 siblings, 2 replies; 12+ messages in thread
From: Andy Shellam @ 2006-04-02 09:06 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Show tables query
2006-04-02 09:06 Show tables query Andy Shellam <andy.shellam@mailnetwork.co.uk>
@ 2006-04-02 09:34 ` Grega Bremec <gregab@p0f.net>
2006-04-02 09:46 ` Re: Show tables query Oleg Bartunov <oleg@sai.msu.su>
1 sibling, 1 reply; 12+ messages in thread
From: Grega Bremec @ 2006-04-02 09:34 UTC (permalink / raw)
To: andy.shellam@mailnetwork.co.uk; +Cc: pgsql-admin
-----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-----
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Show tables query
2006-04-02 09:06 Show tables query Andy Shellam <andy.shellam@mailnetwork.co.uk>
2006-04-02 09:34 ` Re: Show tables query Grega Bremec <gregab@p0f.net>
@ 2006-04-02 09:46 ` Oleg Bartunov <oleg@sai.msu.su>
0 siblings, 0 replies; 12+ messages in thread
From: Oleg Bartunov @ 2006-04-02 09:46 UTC (permalink / raw)
To: Grega Bremec <gregab@p0f.net>; +Cc: andy.shellam@mailnetwork.co.uk; pgsql-admin
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Show tables query
2006-04-02 09:06 Show tables query Andy Shellam <andy.shellam@mailnetwork.co.uk>
@ 2006-04-02 12:48 ` Christopher Browne <cbbrowne@acm.org>
2006-04-02 13:47 ` Re: Show tables query Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
2006-04-02 14:53 ` Re: Show tables query Andy Shellam <andy.shellam@mailnetwork.co.uk>
1 sibling, 2 replies; 12+ messages in thread
From: Christopher Browne @ 2006-04-02 12:48 UTC (permalink / raw)
To: pgsql-admin
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"
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Show tables query
2006-04-02 09:06 Show tables query Andy Shellam <andy.shellam@mailnetwork.co.uk>
2006-04-02 12:48 ` Re: Show tables query Christopher Browne <cbbrowne@acm.org>
@ 2006-04-02 13:47 ` Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
1 sibling, 0 replies; 12+ messages in thread
From: Rajesh Kumar Mallah @ 2006-04-02 13:47 UTC (permalink / raw)
To: Christopher Browne <cbbrowne@acm.org>; +Cc: pgsql-admin
On 4/2/06, Christopher Browne <cbbrowne@acm.org> 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?
>
> 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='BASE TABLE'
;
if you need tables only otherwise it returns the Views also.
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"
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Show tables query
2006-04-02 09:06 Show tables query Andy Shellam <andy.shellam@mailnetwork.co.uk>
2006-04-02 12:48 ` Re: Show tables query Christopher Browne <cbbrowne@acm.org>
@ 2006-04-02 14:53 ` Andy Shellam <andy.shellam@mailnetwork.co.uk>
2006-04-02 15:32 ` Re: Show tables query Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
1 sibling, 1 reply; 12+ messages in thread
From: Andy Shellam @ 2006-04-02 14:53 UTC (permalink / raw)
To: 'Christopher Browne' <cbbrowne@acm.org>; +Cc: pgsql-admin
> 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.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Show tables query
2006-04-02 09:06 Show tables query Andy Shellam <andy.shellam@mailnetwork.co.uk>
2006-04-02 12:48 ` Re: Show tables query Christopher Browne <cbbrowne@acm.org>
2006-04-02 14:53 ` Re: Show tables query Andy Shellam <andy.shellam@mailnetwork.co.uk>
@ 2006-04-02 15:32 ` Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
2006-04-02 15:40 ` Re: Show tables query Andy Shellam <andy.shellam@mailnetwork.co.uk>
2006-04-02 16:46 ` Re: Show tables query Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 12+ messages in thread
From: Rajesh Kumar Mallah @ 2006-04-02 15:32 UTC (permalink / raw)
To: andy.shellam@mailnetwork.co.uk; +Cc: Christopher Browne <cbbrowne@acm.org>; pgsql-admin
On 4/2/06, Andy Shellam <andy.shellam@mailnetwork.co.uk> 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
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Show tables query
2006-04-02 09:06 Show tables query Andy Shellam <andy.shellam@mailnetwork.co.uk>
2006-04-02 12:48 ` Re: Show tables query Christopher Browne <cbbrowne@acm.org>
2006-04-02 14:53 ` Re: Show tables query Andy Shellam <andy.shellam@mailnetwork.co.uk>
2006-04-02 15:32 ` Re: Show tables query Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
@ 2006-04-02 15:40 ` Andy Shellam <andy.shellam@mailnetwork.co.uk>
1 sibling, 0 replies; 12+ messages in thread
From: Andy Shellam @ 2006-04-02 15:40 UTC (permalink / raw)
To: 'Rajesh Kumar Mallah' <mallah.rajesh@gmail.com>; +Cc: pgsql-admin
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 <andy.shellam@mailnetwork.co.uk> 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!
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Show tables query
2006-04-02 09:06 Show tables query Andy Shellam <andy.shellam@mailnetwork.co.uk>
2006-04-02 12:48 ` Re: Show tables query Christopher Browne <cbbrowne@acm.org>
2006-04-02 14:53 ` Re: Show tables query Andy Shellam <andy.shellam@mailnetwork.co.uk>
2006-04-02 15:32 ` Re: Show tables query Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
@ 2006-04-02 16:46 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-04-02 17:38 ` Re: Show tables query Peter Eisentraut <peter_e@gmx.net>
1 sibling, 1 reply; 12+ messages in thread
From: Tom Lane @ 2006-04-02 16:46 UTC (permalink / raw)
To: Rajesh Kumar Mallah <mallah.rajesh@gmail.com>; +Cc: andy.shellam@mailnetwork.co.uk, "Christopher Browne" <cbbrowne@acm.org>; pgsql-admin
"Rajesh Kumar Mallah" <mallah.rajesh@gmail.com> 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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Show tables query
2006-04-02 09:06 Show tables query Andy Shellam <andy.shellam@mailnetwork.co.uk>
2006-04-02 12:48 ` Re: Show tables query Christopher Browne <cbbrowne@acm.org>
2006-04-02 14:53 ` Re: Show tables query Andy Shellam <andy.shellam@mailnetwork.co.uk>
2006-04-02 15:32 ` Re: Show tables query Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
2006-04-02 16:46 ` Re: Show tables query Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-04-02 17:38 ` Peter Eisentraut <peter_e@gmx.net>
2006-04-02 22:28 ` Re: Show tables query Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 12+ messages in thread
From: Peter Eisentraut @ 2006-04-02 17:38 UTC (permalink / raw)
To: pgsql-admin; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Rajesh Kumar Mallah <mallah.rajesh@gmail.com>; andy.shellam@mailnetwork.co.uk, "Christopher Browne" <cbbrowne@acm.org>
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/
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Show tables query
2006-04-02 09:06 Show tables query Andy Shellam <andy.shellam@mailnetwork.co.uk>
2006-04-02 12:48 ` Re: Show tables query Christopher Browne <cbbrowne@acm.org>
2006-04-02 14:53 ` Re: Show tables query Andy Shellam <andy.shellam@mailnetwork.co.uk>
2006-04-02 15:32 ` Re: Show tables query Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
2006-04-02 16:46 ` Re: Show tables query Tom Lane <tgl@sss.pgh.pa.us>
2006-04-02 17:38 ` Re: Show tables query Peter Eisentraut <peter_e@gmx.net>
@ 2006-04-02 22:28 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 12+ messages in thread
From: Tom Lane @ 2006-04-02 22:28 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; +Cc: pgsql-admin@postgresql.org, "Rajesh Kumar Mallah" <mallah.rajesh@gmail.com>; andy.shellam@mailnetwork.co.uk, "Christopher Browne" <cbbrowne@acm.org>
Peter Eisentraut <peter_e@gmx.net> 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
^ permalink raw reply [nested|flat] 12+ messages in thread
* FW: Show tables query
@ 2006-04-02 09:58 Andy Shellam <andy.shellam@mailnetwork.co.uk>
0 siblings, 0 replies; 12+ messages in thread
From: Andy Shellam @ 2006-04-02 09:58 UTC (permalink / raw)
To: pgsql-admin
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!
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2006-04-02 22:28 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-04-02 09:06 Show tables query Andy Shellam <andy.shellam@mailnetwork.co.uk>
2006-04-02 09:34 ` Grega Bremec <gregab@p0f.net>
2006-04-02 09:46 ` Oleg Bartunov <oleg@sai.msu.su>
2006-04-02 12:48 ` Christopher Browne <cbbrowne@acm.org>
2006-04-02 13:47 ` Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
2006-04-02 14:53 ` Andy Shellam <andy.shellam@mailnetwork.co.uk>
2006-04-02 15:32 ` Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
2006-04-02 15:40 ` Andy Shellam <andy.shellam@mailnetwork.co.uk>
2006-04-02 16:46 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-04-02 17:38 ` Peter Eisentraut <peter_e@gmx.net>
2006-04-02 22:28 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-04-02 09:58 FW: Show tables query Andy Shellam <andy.shellam@mailnetwork.co.uk>
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