Re: newbie how to access the information scheme

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