How to list which tables are available?

On Tuesday 14 October 2003 10:53, B.W.H. van Beest wrote:

It seems so elementary, but how I get a list of which tables are
available in a database. I can't find an SQL command for this, but there
must be a way!

In psql use \d or \dt

If you start psql with the -E flag it will show you how it does that.

There are also a number of pg_xxx views that show this sort of thing
(pg_tables, pg_indexes etc). I think these are covered in an appendix of the
manuals.

--
Richard Huxton
Archonet Ltd

On Tue, 2003-10-14 at 04:53, B.W.H. van Beest wrote:

It seems so elementary, but how I get a list of which tables are
available in a database. I can't find an SQL command for this, but there
must be a way!

"$ man psql", then search for the string "list of all tables"

$ psql test1
Welcome to psql 7.3.4, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit

--
-----------------------------------------------------------------
Ron Johnson, Jr. ron.l.johnson@cox.net
Jefferson, LA USA

"Experience hath shewn, that even under the best forms [of
government] those entrusted with power have, in time, and by slow
operations, perverted it into tyranny."
Thomas Jefferson

B.W.H. van Beest writes:

It seems so elementary, but how I get a list of which tables are
available in a database. I can't find an SQL command for this, but there
must be a way!

SELECT * FROM pg_tables;

--
Peter Eisentraut peter_e@gmx.net

Thanks for the answers given. I am discovering the world of SQL ...

Given the nature of all replies (they all refer to something that is
PG-specific), I conclude that there is no generic way (i.e. something
that will hold for all sql-compliant systems).

Is that correct, and moreover, what could be the reason that such an
listing function is not "standard" sql?

Regards,
Bertwim

B.W.H. van Beest wrote:

On Tue, Oct 14, 2003 at 09:02:13PM +0200, B.W.H. van Beest wrote:

Thanks for the answers given. I am discovering the world of SQL ...

Given the nature of all replies (they all refer to something that is
PG-specific), I conclude that there is no generic way (i.e. something
that will hold for all sql-compliant systems).

Actually, the SQL standard _has_ a way to get this information, called the
INFORMATION_SCHEMA. Not all vendors implement it; at least, it's present
in PostgreSQL 7.4. Meanwhile you can use the pg_tables view.

--
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
"Aprende a avergonzarte m���s ante ti que ante los dem���s" (Dem���crito)

On Tue, 2003-10-14 at 14:02, B.W.H. van Beest wrote:

Thanks for the answers given. I am discovering the world of SQL ...

Given the nature of all replies (they all refer to something that is
PG-specific), I conclude that there is no generic way (i.e. something
that will hold for all sql-compliant systems).

Is that correct, and moreover, what could be the reason that such an
listing function is not "standard" sql?

This operation is not, to my knowledge, specified by The Standard.

Thus, it's implementation-specific.

B.W.H. van Beest wrote:

It seems so elementary, but how I get a list of which tables are
available in a database. I can't find an SQL command for this, but there
must be a way!

Thanks.

--
-----------------------------------------------------------------
Ron Johnson, Jr. ron.l.johnson@cox.net
Jefferson, LA USA

"As I like to joke, I may have invented it, but Microsoft made it
popular"
David Bradley, regarding Ctrl-Alt-Del

To get a list of the non-default tables in your database, do this:

SELECT tablename FROM pg_tables WHERE tablename NOT LIKE 'pg_%';

If you really want all of the tables, including the ones PostgreSQL
creates by default, just do this:

SELECT tablename FROM pg_tables;

Hope this helps,
chet@crashed.net

"B.W.H. van Beest" <bwvb@xs4all.nl> wrote in message news:<3f8bc79c$0$58708$e4fe514c@news.xs4all.nl>...

On Tue, 14 Oct 2003, Ron Johnson wrote:

On Tue, 2003-10-14 at 14:02, B.W.H. van Beest wrote:

Thanks for the answers given. I am discovering the world of SQL ...

Given the nature of all replies (they all refer to something that is
PG-specific), I conclude that there is no generic way (i.e. something
that will hold for all sql-compliant systems).

Is that correct, and moreover, what could be the reason that such an
listing function is not "standard" sql?

This operation is not, to my knowledge, specified by The Standard.

Thus, it's implementation-specific.

Actually, as of 7.4 we implement the spec standard information_schema,
which is the spec way of doing it. It's just fairly new, so most
databases probably don't support it yet.