On Wed, Jan 6, 2010 at 2:55 PM, Richard Broersma
> 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
> 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 Lane||Date: 2010-01-06 23:33:32|
|Subject: Re: Error on Vacuum? |
|Previous:||From: Richard Broersma||Date: 2010-01-06 22:55:12|
|Subject: Re: SQL for listing the tables in a specific database|