Obtendo dados de tabelas, colunas e relacionamentos no PostgreSQL

Artigo que apresenta algumas instruções SQL para se obter informações sobre tabelas, colunas e relacionamentos de tabelas em bancos de dados PostgreSQL.

Elefante
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

12 comentários

Anônimo disse...

Rubens muito bacana o post.

Agora, gostaria de saber se tem algum atributo que me informe o tamanho do campo de acordo com o que foi declarado. Por exemplo, declarei um campo deste forma: codigo numeric(5) default 0.
Ou seja, tem algum atributo que vai retornar 5 que é o número de dígitos?

Desde já agradeço.

Anônimo disse...

sim, verifiquei...
no caso desse campo que informei no post anterior:
codigo numeric(5) default 0
o atributo attlen é -1

no meu caso, estou desenvolvendo uma aplicação em PHP, utilizando o PostgreSQL 8.1. o que quero fazer é o seguinte... de dentro do código em PHP, eu quero resgatar o tamanho desse campo numérico, a precisão que foi declarada, ou seja, no caso 5.

Lia :) disse...

O sql de chave estrangeira não detecta quando a chave é dupla [2]

tem como resolver ?

Rubens Takiguti Ribeiro (autor do blog) disse...

Eduardo e Lia,

De fato, a SQL serve apenas para obter relacionamentos em que a chave é única. Particularmente, prefiro trabalhar com tabelas que possuem uma chave primária única.

Uma forma de obter o segundo, terceiro ou quarto campo do relacionamento é trocando o "1" da SQL por "2", "3", "4", etc. Note que são dois lugares que precisam ser trocados.

Anônimo disse...

Realmente, não é uma boa ideia usar chaves primárias duplas. Por quê? Senão quando for relacionar essa tabela com outra, você vai ter uma chave estrangeira dupla. Pensa na salada...

Os DBAs tem feito é criar uma chave primária única, numérica e sequencial (famosos AUTO_INCREMENT, SERIAL, IDENTITY), e depois adicionam uma chave única (UNIQUE INDEX) para a combinação de campos que não podem se repetir.

Belo artigo, parabéns!!!

Caixa Ferramenta disse...

Além disso, tenho uma aplicação que tem apagar um campo de uma tabela especifica caso este campo exista, caso contrário, deve passar para a próxima tabela. Tem algum código para fazer isso?
Valeu.

Rubens Takiguti Ribeiro (autor do blog) disse...

Olá, Caixa Ferramenta

Sobre obter as tabelas de um esquema específico, dê uma pesquisada sobre a coluna "relnamespace".

Quanto à lógica da aplicação, não sei se é possível fazer o que quer apenas com queries SQL. Talvez precise colocar esta lógica na própria aplicação.

Em geral, não é comum quebrar tabelas (separando-as por nomes como "usuarios1", "usuarios2", etc). Se está fazendo isso, sugiro que dê uma olhada sobre replicação, bancos de dados distribuídos e alta disponibilidade.