Working with System Catalogs

SELECT relname, relkind, reltuples, pg_size_pretty(pg_relation_size(oid))
  FROM pg_class WHERE relkind IN ('r','p') ORDER BY pg_relation_size(oid) DESC;

SELECT attname, format_type(atttypid, atttypmod), attnotnull
  FROM pg_attribute
 WHERE attrelid = 'orders'::regclass AND attnum > 0 AND NOT attisdropped
 ORDER BY attnum;

SELECT conname, contype, pg_get_constraintdef(oid)
  FROM pg_constraint WHERE conrelid = 'orders'::regclass;

SELECT i.indexrelid::regclass AS index, indisunique, indisprimary,
       pg_get_indexdef(indexrelid)
  FROM pg_index i WHERE indrelid = 'orders'::regclass;

SELECT datname, pg_size_pretty(pg_database_size(datname)), datcollate, datctype
  FROM pg_database WHERE NOT datistemplate;

SELECT nspname, nspowner::regrole FROM pg_namespace
 WHERE nspname NOT LIKE 'pg\_%' ESCAPE '\' AND nspname <> 'information_schema';

SELECT p.proname, pg_get_function_identity_arguments(p.oid)
  FROM pg_proc p JOIN pg_namespace n ON n.oid = p.pronamespace
 WHERE n.nspname = 'public' AND p.prokind = 'f';

SELECT typname, typcategory FROM pg_type WHERE typcategory = 'E';   -- enums

SELECT rolname, rolcanlogin, rolconnlimit, rolvaliduntil FROM pg_roles;

SELECT schemaname, tablename, tableowner FROM pg_tables WHERE schemaname = 'public';

SELECT tablename, indexname, indexdef FROM pg_indexes WHERE schemaname='public';

SELECT table_schema, table_name, column_name, data_type
  FROM information_schema.columns WHERE table_schema = 'public';

SELECT name, setting, unit, source, sourcefile, pending_restart
  FROM pg_settings WHERE name LIKE '%memory%' OR name LIKE '%wal%';