How to list which tables are available?

[B.W.H. van Beest]

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.

Bertwim

[Richard Huxton]

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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Ron Johnson]

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. ro***********@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
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Peter Eisentraut]

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 pe*****@gmx.net
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[B.W.H. van Beest]

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:

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.

Bertwim

[Alvaro Herrera Munoz]

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)

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Richard Huxton]

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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Ron Johnson]

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. ro***********@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
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Ron Johnson]

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. ro***********@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
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Chet Luther]

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,
ch**@crashed.net

"B.W.H. van Beest" <bw**@xs4all.nl> wrote in message news:<3f***********************@news.xs4all.nl>...

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.

Bertwim

[Peter Eisentraut]

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 pe*****@gmx.net
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[B.W.H. van Beest]

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:

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.

Bertwim

[scott.marlowe]

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.
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Alvaro Herrera Munoz]

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)

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Ron Johnson]

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. ro***********@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
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Chet Luther]

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,
ch**@crashed.net

"B.W.H. van Beest" <bw**@xs4all.nl> wrote in message news:<3f***********************@news.xs4all.nl>...

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.

Bertwim

[scott.marlowe]

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.
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org