Thread: RE: [GENERAL] 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

> 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
    -------------------------------------------------------------------