PostgreSQL Prático/Metadados

Metadados são dados sobre dados.

Uma consulta normal retorna informações existentes em tabelas, já uma consulta sobre os metadados retorna informações sobre os bancos, os objetos dos bancos, os campos de tabelas, seus tipos de dados, seus atributos, suas constraints, etc.

Retornar Todas as Tabelas do banco e esquema atual

SELECT schemaname AS esquema, tablename AS tabela, tableowner AS dono
 FROM pg_catalog.pg_tables
 WHERE schemaname NOT IN ('pg_catalog', 'information_schema', 'pg_toast')
 ORDER BY schemaname, tablename

Informações de Todos os Tablespaces

SELECT spcname, pg_catalog.pg_get_userbyid(spcowner) AS spcowner, spclocation
 FROM pg_catalog.pg_tablespace

Retornar banco, dono, codificação, comentários e tablespace

SELECT pdb.datname AS banco, 
 pu.usename AS dono, 
 pg_encoding_to_char(encoding) AS codificacao,
 (SELECT description FROM pg_description pd WHERE pdb.oid=pd.objoid) AS comentario,
 (SELECT spcname FROM pg_catalog.pg_tablespace pt WHERE pt.oid=pdb.dattablespace) AS tablespace
 FROM pg_database pdb, pg_user pu WHERE pdb.datdba = pu.usesysid ORDER BY pdb.datname

Tabelas, donos, comentários, registros e tablespaces de um schema

SELECT c.relname as tabela, 
 pg_catalog.pg_get_userbyid(c.relowner) AS dono, 
 pg_catalog.obj_description(c.oid, 'pg_class') AS comentario, reltuples::integer as registros,
 (SELECT spcname FROM pg_catalog.pg_tablespace pt WHERE pt.oid=c.reltablespace) AS tablespace
 FROM pg_catalog.pg_class c
 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
 WHERE c.relkind = 'r' AND nspname='public'
 ORDER BY c.relname

Mostrar Sequences de um Esquema

SELECT c.relname AS seqname, u.usename AS seqowner, pg_catalog.obj_description(c.oid, 'pg_class') AS seqcomment,
    (SELECT spcname FROM pg_catalog.pg_tablespace pt WHERE pt.oid=c.reltablespace) AS tablespace
     FROM pg_catalog.pg_class c, pg_catalog.pg_user u, pg_catalog.pg_namespace n
     WHERE c.relowner=u.usesysid AND c.relnamespace=n.oid
     AND c.relkind = 'S' AND n.nspname='public' ORDER BY seqname

Mostrar Sequences de um Esquema

SELECT c.relname AS seqname, u.usename AS seqowner, pg_catalog.obj_description(c.oid, 'pg_class') AS seqcomment,
    (SELECT spcname FROM pg_catalog.pg_tablespace pt WHERE pt.oid=c.reltablespace) AS tablespace
     FROM pg_catalog.pg_class c, pg_catalog.pg_user u, pg_catalog.pg_namespace n
     WHERE c.relowner=u.usesysid AND c.relnamespace=n.oid
     AND c.relkind = 'S' AND n.nspname='public' ORDER BY seqname

Mostrar Esquemas e respectivas tabelas do Banco atual:

SELECT n.nspname as esquema, c.relname as tabela  FROM pg_namespace n, pg_class c
 WHERE n.oid = c.relnamespace
   and c.relkind = 'r'     -- no indices
   and n.nspname not like 'pg\\_%' -- no catalogs
   and n.nspname != 'information_schema' -- no information_schema
 ORDER BY nspname, relname

Dado o banco de dados, qual o seu diretório:

select datname, oid from pg_database;

Dado a tabela, qual o seu arquivo:

select relname, relfilenode from pg_class;

Tamanho em bytes de um banco:

select pg_database_size('banco');

Tamanho em bytes de uma tabela:

pg_total_relation_size('tabela')

Tamanho em bytes de tabela ou índice:

pg_relation_size('tabelaouindice')

Lista donos e bancos:

SELECT rolname as dono, datname as banco

FROM pg_roles, pg_database

WHERE pg_roles.oid = datdba

ORDER BY rolname, datname;

Nomes de bancos:

select datname from pg_database where datname not in ('template0','template1') order by 1