Postgres Pro
Downloads
Home   >   mailing lists

Thread: newbie how to access the information scheme

newbie how to access the information scheme

From
"frank ernest"
Date:
Hello, I'd like to see all the tables in my data base,
but can't figure out how to access th information scheme.

Thanks

Re: newbie how to access the information scheme

From
John McKown
Date:
I normally do the command (in psql)

\d+

But if you want an actual SQL statement, the above invokes:

SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm'
THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN
'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' END
as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
  pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) as "Size",
  pg_catalog.obj_description(c.oid, 'pg_class') as "Description"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','v','m','S','f','')
      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;

I found the above by using the command: psql -E

On Tue, Feb 24, 2015 at 12:48 PM, frank ernest <doark@mail.com> wrote:
> Hello, I'd like to see all the tables in my data base,
> but can't figure out how to access th information scheme.
>
> Thanks
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general



--
He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

Re: newbie how to access the information scheme

From
Tomas Vondra
Date:
On 24.2.2015 19:58, John McKown wrote:
> I normally do the command (in psql)
>
> \d+
>
> But if you want an actual SQL statement, the above invokes:
>
> SELECT n.nspname as "Schema",
>   c.relname as "Name",
>   CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm'
> THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN
> 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' END
> as "Type",
>   pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
>   pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) as "Size",
>   pg_catalog.obj_description(c.oid, 'pg_class') as "Description"
> FROM pg_catalog.pg_class c
>      LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
> WHERE c.relkind IN ('r','v','m','S','f','')
>       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;
>
> I found the above by using the command: psql -E

Or just use the  information_schema like this:

   select table_schema, table_name from information_schema.tables;

It's also possible to get mostly the same info using pg_class catalog:

   select relname from pg_class where relkind = 'r';

but that may require a bit more work, if you want schema names too for
example (as the query executed by psql illustrates).

regards


--
Tomas Vondra                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: newbie how to access the information scheme

From
"frank ernest"
Date:
Thanks