Re: [GENERAL] newbie how to access the information scheme

2015-02-25 Thread frank ernest 
Thanks


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] newbie how to access the information scheme

2015-02-24 Thread frank ernest 
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

Re: [GENERAL] newbie how to access the information scheme

2015-02-24 Thread John McKown 
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 do...@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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] newbie how to access the information scheme

2015-02-24 Thread Tomas Vondra 
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 Vondrahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training  Services


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general