Re: SQL Command - To List Tables ?

On Wed, 2003-08-27 at 21:03, Heath Tanner wrote:
> On Wednesday, August 27, 2003, at 09:17  AM, Carmen Gloria Sepulveda
> Dedes wrote:
>
> > Hello.  I type \dt as user postgres, but it show only the tables within
> > public schema.  I need to know all tables (like v$_  or dba_ or user_
> > in oracle).   How I do that?
>
> Start psql with the echo flag:
>
> [inligo:~] heath% psql -E dpe
> ********* QUERY **********
> BEGIN; SELECT usesuper FROM pg_catalog.pg_user WHERE usename = 'heath';
> COMMIT
> **************************
>
> Welcome to psql 7.3.4, the PostgreSQL interactive terminal.
>
> Type:  \copyright for distribution terms
>         \h for help with SQL commands
>         \? for help on internal slash commands
>         \g or terminate with semicolon to execute query
>         \q to quit
>
> dpe=# \dt
> ********* QUERY **********
> 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",
>    u.usename as "Owner"
> FROM pg_catalog.pg_class c
>       LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
>       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
> WHERE c.relkind IN ('r','')
>        AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
>        AND pg_catalog.pg_table_is_visible(c.oid)
> ORDER BY 1,2;
> **************************

Isn't that big CASE statement unnecessary, since you also have
  c.relkind IN ('r','')

--
-----------------------------------------------------------------
Ron Johnson, Jr. ron.l.johnson@cox.net
Jefferson, LA USA

484,246 sq mi are needed for 6 billion people to live, 4 persons
per lot, in lots that are 60'x150'.
That is ~ California, Texas and Missouri.
Alternatively, France, Spain and The United Kingdom.