How to find names of tables in a database?

Hi -- I've been dabbling in PostgreSQL on and off for a few months
now. At
this point I can get it to do a number of neat things, but I've run
into
one that is a stumper.

I'm looking for a way that my Perl program can look up the names of
the
tables in a database. Is there a way to do this? I've looked through
the
documentation on the PostgreSQL website, but didn't find any way to do
this. It seems like there must be a query like "SELECT table_names
FROM
my_database" to do this, but if there is, I haven't found it yet.

(My apologies if this has been discussed recently. I was on this
mailing
list for a while, but have been off it for a month or so.)

Thanks for any help you can offer!

-Valerie Mates

I thought for sure this was on the FAQ...
What you are looking for is the behavior of psql's \dt command.
You can look at the psql source to get the exact query, but it's
something like this:
SELECT * FROM pg_class
The \dt command filters out the system (WHERE rel_name NOT LIKE 'pg_%')
tables and some other things so I'd look at it otherwise you get a few
hundred tables in an empty database.
-DEJ

"Jackson, DeJuan" wrote:

I'm looking for a way that my Perl program can look up the names of
the
tables in a database. Is there a way to do this?

I thought for sure this was on the FAQ...

Maybe it isn't yet, but it is in "Administrative Tasks" chapter of the users's manual (along with other useful things).

--Gene

On Wed, 14 Oct 1998, Valerie Mates wrote:

Hi -- I've been dabbling in PostgreSQL on and off for a few months now. At
this point I can get it to do a number of neat things, but I've run into
one that is a stumper.

I'm looking for a way that my Perl program can look up the names of the
tables in a database. Is there a way to do this? I've looked through the
documentation on the PostgreSQL website, but didn't find any way to do
this. It seems like there must be a query like "SELECT table_names FROM
my_database" to do this, but if there is, I haven't found it yet.

select relname from pg_class where relname not like 'pg_%';

will give you all user-defined tables.

Simon.

(My apologies if this has been discussed recently. I was on this mailing
list for a while, but have been off it for a month or so.)

Thanks for any help you can offer!

-Valerie Mates

-------------------------------------------------------------------
Valerie Mates * Web Developer * http://www.valeriemates.com
valerie@cyberspace.org * (734) 995-6716
-------------------------------------------------------------------

--
There are three kinds of people in this world: Those who can count,
and those who can't.

Simon Drabble mailto:madlather@syspac.com
http://fiddletrudge.dyn.ml.org/
KreatAPayj -- KAP2/login.html
LizardCam -- LizardCam/LizCam.html

I thought for sure this was on the FAQ...
What you are looking for is the behavior of psql's \dt command.
You can look at the psql source to get the exact query, but it's
something like this:
SELECT * FROM pg_class
The \dt command filters out the system (WHERE rel_name NOT LIKE 'pg_%')
tables and some other things so I'd look at it otherwise you get a few
hundred tables in an empty database.

It's in there(the FAQ):

3.23) How do I get a list of tables, or other things I can see in psql?

See the file pgsql/src/bin/psql/psql.c. It contains SQL commands that
generate the output for psql's backslash commands.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

At 07:43 PM 10/14/98 -0400, Bruce Momjian wrote:

I thought for sure this was on the FAQ...
What you are looking for is the behavior of psql's \dt command.
You can look at the psql source to get the exact query, but it's
something like this:
SELECT * FROM pg_class
The \dt command filters out the system (WHERE rel_name NOT LIKE 'pg_%')
tables and some other things so I'd look at it otherwise you get a few
hundred tables in an empty database.

It's in there(the FAQ):

3.23) How do I get a list of tables, or other things I can see in psql?

See the file pgsql/src/bin/psql/psql.c. It contains SQL commands that
generate the output for psql's backslash commands.

Yup. I found this entry in the FAQ, but unfortunately I'm using a Linux
box where the PostgreSQL binaries were installed from an RPM, so the psql
source isn't anywhere to be found.

Anyway, thanks to you and to the other the kind souls who sent me the
information I needed. I think I'm all set now. Much appreciated!

-Valerie

-------------------------------------------------------------------
Valerie Mates * Web Developer * http://www.valeriemates.com
valerie@cyberspace.org * (734) 995-6716
-------------------------------------------------------------------