Obtendo dados de tabelas, colunas e relacionamentos no PostgreSQL

Introdução

No post anterior, vimos como consultar informações sobre tabelas, colunas e relacionamentos em bancos de dados MySQL. Neste post serão apresentados os recursos correspondentes no banco de dados PostgreSQL.

Diferente do MySQL, o PostgreSQL não possui um banco de dados reservado chamado information_schema. Ao invés disso, possui algumas tabelas reservadas prefixadas por "pg_".

SQLs úteis para PostgreSQL

1 - Obter informações sobre as tabelas de um banco de dados ou de uma tabela específica:

SELECT
  c.*,
  obj_description(c.oid, 'pg_class') AS comentario,
  pg_total_relation_size(c.relname) as tamanho
FROM
  pg_class c LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace)
WHERE
  c.relkind = 'r' AND nspname = 'public'
ORDER BY
  c.relname;

Valores retornados:

• relname - Nome da tabela
• relnamespace - OID do namespace
• reltype - OID do tipo de tabela
• relowner - OID do dono da tabela
• relam - OID do tipo de índice (não é usado em caso de tabelas)
• relfilenode - OID do arquivo em disco, caso exista
• reltablespace - OID do tablespace da tabela (ou zero, caso seja o padrão)
• relpages - Tamanho estimado da tabela em unidade de páginas (blocksz)
• reltuples - Estimativa do número de linhas da tabela
• reltoastrelid - OID da tabela associada a esta e que armazena dados extras
• reltoastidxid - Caso seja uma tabela extra (toast), indica o OID dela
• relhasindex - Indica se a tabela possui (ou possuia recentemente) um índice
• relisshared - Indica se a tabela é compartilhada entre todos os bancos de dados
• relkind - r = tabela comum, i = índice, S = sequência, v = visão, c = tipo composto, s = especial, t = tabela TOAST
• relnatts - Número de colunas
• relchecks - Número de restrições (constraint)
• reltriggers - Número de gatilhos (triggers)
• relukeys - Valor não utilizado
• relfkeys - Valor não utilizado
• relrefs - Valor não utilizado
• relhasoids - Indica se é gerado um OID para cada linha da tabela
• relhaspkey - Indica se possui uma chave primária
• relhasrules - Indica se possui regras
• relhassubclass - Indica se a tabela possui outras que herdam dela
• relacl - Privilégios de acesso.
• reloptions - Outras opções
• comentario - Comentário da tabela, caso exista
• tamanho - Tamanho da tabela em bytes

2 - Obter as colunas (campos) de uma tabela de um banco de dados:

SELECT
  a.*,
  (CASE (a.atttypmod < 0) WHEN TRUE THEN a.attlen ELSE a.atttypmod - 4 END) AS tamanho,
  pg_get_expr(d.adbin, d.adrelid) AS padrao,
  col_description(c.oid, a.attnum) AS comentario
FROM
  pg_class c
  INNER JOIN pg_attribute a ON (c.oid = a.attrelid)
  INNER JOIN pg_attrdef d ON (c.oid = d.adrelid AND d.adnum = a.attnum)
WHERE
  c.relname = 'nome_da_tabela' AND
  a.attnum > 0
ORDER BY
  a.attnum ASC

Valores retornados:

• attrelid - OID da tabela que possui a coluna
• attname - Nome da coluna
• atttypid - OID do tipo de valor
• attlen - Tamanho da coluna em bytes ou -1, caso ela tenha um tipo textual
• attnum - Número da coluna na tabela
• attndims - Número de dimensões da coluna
• atttypmod - Tamanho da coluna de acordo com o tipo
• attnotnull - Indica se o campo não pode ser nulo
• atthasdef - Indica se o campo possui valor padrão
• attisdropped - Indica se a coluna foi apagada
• attislocal - Indica se a coluna é local
• tamanho - Devolve o tamanho em bytes de campos numéricos ou número de caracteres em campos textuais
• padrao - Valor padrão da coluna
• comentario - Comentário da coluna, caso exista

3 - Obter a chave primária de uma tabela

SELECT a.attname AS chave_pk
FROM pg_class c
  INNER JOIN pg_attribute a ON (c.oid = a.attrelid)
  INNER JOIN pg_index i ON (c.oid = i.indrelid)
WHERE
  i.indkey[0] = a.attnum AND
  i.indisprimary = 't' AND
  c.relname = 'nome_da_tabela'

4 - Obter os relacionamentos entre tabelas (chaves estrangeiras) de uma tabela:

SELECT   
  a.attname AS atributo,   
  clf.relname AS tabela_ref,   
  af.attname AS atributo_ref   
FROM pg_catalog.pg_attribute a   
  JOIN pg_catalog.pg_class cl ON (a.attrelid = cl.oid AND cl.relkind = 'r')
  JOIN pg_catalog.pg_namespace n ON (n.oid = cl.relnamespace)   
  JOIN pg_catalog.pg_constraint ct ON (a.attrelid = ct.conrelid AND   
       ct.confrelid != 0 AND ct.conkey[1] = a.attnum)   
  JOIN pg_catalog.pg_class clf ON (ct.confrelid = clf.oid AND clf.relkind = 'r')
  JOIN pg_catalog.pg_namespace nf ON (nf.oid = clf.relnamespace)   
  JOIN pg_catalog.pg_attribute af ON (af.attrelid = ct.confrelid AND   
       af.attnum = ct.confkey[1])   
WHERE   
  cl.relname = 'nome_da_tabela'

Valores retornados:

• atributo - Nome da chave estrangeira (FK)
• tabela_ref - Tabela de onde veio a chave
• atributo_ref - Nome do atributo na tabela original