Get list of databases/tables/views in Postgresql
Posted by sergey - 21/08/08 at 09:08:59 pmGet list of databases -
select datname FROM pg_catalog.pg_database;
This displays tables, views, indexes and sequences except postgres internal tables/views
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;
Same, but only for tables
select c.relname 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 NOT IN ('pg_catalog', 'pg_toast')
AND pg_catalog.pg_table_is_visible(c.oid);
This shows table columns of a given table and their types -
SELECT
attname as Field,
(select typname from pg_type where oid=pga.atttypid) as Type
FROM pg_attribute pga
WHERE
attrelid=(select oid from pg_class where relname=$table) and
attisdropped=false and
attnum>0
No Comments yet »
RSS feed for comments on this post. TrackBack URI
Leave a comment