Skip site navigation (1) Skip section navigation (2)

Re: SQL for listing the tables in a specific database

From: Lonni J Friedman <netllama(at)gmail(dot)com>
To: Richard Broersma <richard(dot)broersma(at)gmail(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: SQL for listing the tables in a specific database
Date: 2010-01-06 23:04:20
Message-ID: 7c1574a91001061504n2f906de3k52d62806bcce4321@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
On Wed, Jan 6, 2010 at 2:55 PM, Richard Broersma
<richard(dot)broersma(at)gmail(dot)com> wrote:
> On Wed, Jan 6, 2010 at 2:46 PM, Lonni J Friedman <netllama(at)gmail(dot)com> wrote:
>
>> I've been googling for a while, and I can't seem to find a solution.
>> What I'd like to do is obtain a list of the tables that are part of a
>> specific database instance.  Google has provided me with numerous ways
>> of listing every table across all the database instances, but not a
>> way to isolate the dataset to just one database.
>>
>> Here's what I've found thus far, which lists every table from every database:
>>
>> select * from pg_tables
>> select table_name from information_schema.tables where table_schema = 'public' ;
>
> Here is the query that my version of psql issues:
>
> SELECT n.nspname as "Schema",
>  c.relname as "Name",
>  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i'
> THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as
> "Type",
>  pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
> FROM pg_catalog.pg_class c
>     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
> WHERE c.relkind IN ('r','')
>      AND n.nspname <> 'pg_catalog'
>      AND n.nspname <> 'information_schema'
>      AND n.nspname !~ '^pg_toast'
>  AND pg_catalog.pg_table_is_visible(c.oid)
> ORDER BY 1,2;
>

Duh.  For some reason, I got it into my head that the query associated
with \dt was too generic to do what I wanted.  Thanks!

In response to

pgsql-novice by date

Next:From: Tom LaneDate: 2010-01-06 23:33:32
Subject: Re: Error on Vacuum?
Previous:From: Richard BroersmaDate: 2010-01-06 22:55:12
Subject: Re: SQL for listing the tables in a specific database

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group