Re: SQL for listing the tables in a specific database

On Wed, Jan 6, 2010 at 2:55 PM, Richard Broersma
<richard.broersma@gmail.com> wrote:
> On Wed, Jan 6, 2010 at 2:46 PM, Lonni J Friedman <netllama@gmail.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!