How to list which tables are available?

On Tuesday 14 October 2003 10:53, B.W.H. van Beest wrote:[color=blue]
> 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![/color]

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 Join Bytes!

On Tue, 2003-10-14 at 04:53, B.W.H. van Beest wrote:[color=blue]
> 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![/color]

"$ 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. Join Bytes!
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

B.W.H. van Beest writes:
[color=blue]
> 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![/color]

SELECT * FROM pg_tables;

--
Peter Eisentraut Join Bytes!


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to Join Bytes! so that your
message can get through to the mailing list cleanly

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:[color=blue]
> 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
>[/color]

On Tue, Oct 14, 2003 at 09:02:13PM +0200, B.W.H. van Beest wrote:[color=blue]
> 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).[/color]

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

On Tuesday 14 October 2003 10:53, B.W.H. van Beest wrote:[color=blue]
> 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![/color]

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 Join Bytes!

On Tue, 2003-10-14 at 04:53, B.W.H. van Beest wrote:[color=blue]
> 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![/color]

"$ 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. Join Bytes!
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

On Tue, 2003-10-14 at 14:02, B.W.H. van Beest wrote:[color=blue]
> 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?[/color]

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

Thus, it's implementation-specific.
[color=blue]
> B.W.H. van Beest wrote:[color=green]
> > 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.[/color][/color]

--
-----------------------------------------------------------------
Ron Johnson, Jr. Join Bytes!
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

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,
Join Bytes!

"B.W.H. van Beest" <bwvb@xs4all.nl> wrote in message news:<3f8bc79c$0$58708$e4fe514c@news.xs4all.nl>...[color=blue]
> 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[/color]

B.W.H. van Beest writes:
[color=blue]
> 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![/color]

SELECT * FROM pg_tables;

--
Peter Eisentraut Join Bytes!


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to Join Bytes! so that your
message can get through to the mailing list cleanly

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:[color=blue]
> 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
>[/color]

On Tue, 14 Oct 2003, Ron Johnson wrote:
[color=blue]
> On Tue, 2003-10-14 at 14:02, B.W.H. van Beest wrote:[color=green]
> > 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?[/color]
>
> This operation is not, to my knowledge, specified by The Standard.
>
> Thus, it's implementation-specific.[/color]

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 Join Bytes!

On Tue, Oct 14, 2003 at 09:02:13PM +0200, B.W.H. van Beest wrote:[color=blue]
> 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).[/color]

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

On Tue, 2003-10-14 at 14:02, B.W.H. van Beest wrote:[color=blue]
> 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?[/color]

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

Thus, it's implementation-specific.
[color=blue]
> B.W.H. van Beest wrote:[color=green]
> > 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.[/color][/color]

--
-----------------------------------------------------------------
Ron Johnson, Jr. Join Bytes!
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

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,
Join Bytes!

"B.W.H. van Beest" <bwvb@xs4all.nl> wrote in message news:<3f8bc79c$0$58708$e4fe514c@news.xs4all.nl>...[color=blue]
> 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[/color]

On Tue, 14 Oct 2003, Ron Johnson wrote:
[color=blue]
> On Tue, 2003-10-14 at 14:02, B.W.H. van Beest wrote:[color=green]
> > 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?[/color]
>
> This operation is not, to my knowledge, specified by The Standard.
>
> Thus, it's implementation-specific.[/color]

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 Join Bytes!