Postgres – Instalação no Línux

Instalação

Para instalar o PostgreSQL no Ubuntu digite o comando:

Use sudo se não estiver com usuário root

sudo apt-get install postgresql-8.4

(O postgres é instalado com usuário “postgres” e senha em branco “”)

Mudando senha do postgres

sudo su postgres psql (startando o postgres)

$ psql postgres (modo edição)

No console do psql (alterando a senha)

ALTER USER postgres WITH PASSWORD 'NOVA SENHA';

Sair

Restart o serviço:

invoke-rc.d postgresql-8.4 restart

ou

root@gaya:/etc/init.d# sudo service postgresql-8.4 start

root@gaya:/etc/init.d# sudo service postgresql-8.4 stop

Sair:

\q

ou CTRL+D

Configurando o postgresql.conf (PARA ACESSO REMOTO!!!)

Se estiver com o root: vi /etc/postgresql/8.4/main/postgresql.conf

sudo vi /etc/postgresql/8.4/main/postgresql.conf

Localizar a linha:

listen_addresses = 'localhost'

e mudar para

listen_addresses = '*' (aceita tudo)

remova o carácter de comentário (#) da linha.

A opção listen_addresses permite definir qual host pode se conectar no servidor, exemplo: localhost somente para conexões locais, * para qualquer host ou um ip especifico como: 192.168.1.236
Configurando pg_hba.conf

sudo vi /etc/postgresql/8.4/main/pg_hba.conf

Adicionar no fim do arquivo a linha:

host all all 192.168.1.0/24 md5

ou para abrir para qualquer rede:

host all all 0.0.0.0/0 md5

ou

host all all 0.0.0.0/0 trust

Essa configuração especifica na ordem:

host: Define regras para protocolo TCP/IP, essa opção pode ser host, local ou hostssl
all: Define qual banco dados que essa regra se aplica, se definido all será para todos.
all: Define qual usuário tem permissão para usar essa regra, se definido all será para todos.
192.168.1.0/24: Padrão CIDR, define qual faixa de ip’s podem se conectar no servidor através do padrão CIDR.
md5: Método de autenticação, md5 padrão, trust para aceitar, reject para negar e mais algumas outras opções.

Reiniciar o PostgreSQL

sudo /etc/init.d/postgresql-8.2 restart

root@gaya:/etc/init.d# sudo service postgresql-8.4 start

Usuários de banco de dados

usuário – CRIAR UM NOVO

Para pode criar um novo usuário é necessário estar logado com o usuário postgres.

sudo su postgres

Existem duas formas de criar e deletar um usuário, uma através de shell e outra através do cliente postgre.
Shell

createuser nei
createuser patrick

Cliente postgres

psql CREATE USER nei; CREATE USER patrick;

As duas opções criam nei e patrick como superusuários.

Exemplos

Definindo password

CREATE USER nei PASSWORD ‘nei’;

Definindo privilégios de superusuário, permissão para criar databases e roles

CREATE USER nei SUPERUSER INHERIT CREATEDB CREATEROLE;

http://pgdocptbr.sourceforge.net/pg80/sql-createuser.html

Deletar usuário
Shell

dropuser nei dropuser patrick

Cliente postgres

psql DROP USER nei; DROP USER patrick;

Alterar usuário

ALTER USER nei PASSWORD '123';

http://pgdocptbr.sourceforge.net/pg80/sql-alteruser.html

BANCO – CRIAR UM NOVO

Criando usuario e banco de dados
sudo -u postgres createuser -D -A -P myuser

Para criar o banco de dados com suporte ao conjunto de caracteres ISO-8859-1:
sudo -u postgres createdb -O myuser mydb -E LATIN1

Excluindo base de dados
sudo -u postgres dropdb mydb

Instalando e configurando o PostgreSQL 8.3 no Ubuntu

Original em: http://jmmwrite.wordpress.com/2008/05/08/instalando-e-configurando-o-postgresql-83-no-ubuntu/

No meu ambiente de desenvolvimento, eu sempre utilizo dois bancos: o Postgres e o DB2. Basicamente devido ao fato de minha instalação de DB2 ser pesada pois tenho várias configurações de Data Warehouse e um banco muito carregado, o que torna o banco mais pesado para meu simples desktop. Então, para debugar meus softwares, vou com meu postgres levinho mesmo.

Minha idéia aqui é mostrar como instalar e configurar o PostgreSQL 8.3 no Ubuntu 8.04. As configurações são as mesmas para a instalação em Windows, a única diferença obvia é que você irá ter que ir ao site do postgres e baixar o Installer do Windows.

Vamos lá, iniciamos a instalação com o comando:

$ sudo apt-get install postgresql-8.3 postgresql-client-8.3

Recomendo também instalar o pgAdmin, que é uma ferramenta para administrar o postgres:

$ sudo apt-get install pgadmin3 pgadmin3-data

Algo que aconteceu comigo na migração para o Ubuntu 8.04, foi que tive que remover o Postgres 8.2 (apt-get purge postgresql-8.2) para conseguir iniciar o 8.3 corretamente. Se você concluir meus passos aqui e mesmo assim não conseguir conectar no Postgres, recebendo algum erro, provavelmente terá que dar o purge. (talvez com mais tempo de pesquisa eu poderia descobrir qual era o problema… se alguem passar por isso me diga please).

O próximo passo é setar uma senha para o usuário postgres com os seguintes comandos:

sudo su postgres -c psql postgres
ALTER USER postgres WITH PASSWORD ‘password’;
\q

O primeiro comando chama o utilitário psql com o usuário postgres e conecta no postgres especificamente no database postgres.
O segundo comando altera a senha do usuário postgres.
O terceiro comando finaliza o psql.
Note que a palavra password deve ser substituida pela password que você desejar.

Feito a instalação e mudança de senha do usuário postgres, você estará apto a desenvolver um trabalho no seu computador conectando normalmente ao postgres, porém, se a idéia é disponibilizar o acesso ao banco para receber conexões de outras máquinas, você vai ter que alterar dois arquivos para isso. Para isso, vá para o diretório /etc/postgres/8.3/main

Edite o arquivo postgresql.conf

Na linha listen_addresses, troque o localhost por *, ficando a linha assim:

listen_addresses = ‘*’

Dessa forma seu postgres vai “escutar” não só conexoes provenientes da sua própria máquina.

A próxima configuração no mesmo arquivo é habilitar a encriptação de passwords, para fazer isso descomente a linha abaixo simplesmente removendo o # da frente dela:

password_encryption = on

Finalmente a próxima configuração é no arquivo pg_hba.conf. Neste arquivo você consegue restringir o acesso ao seu banco de dados por IP. Normalmente queremos liberar o acesso para todos os IPs em uma faixa, no meu exeplo, quero liberar para todas as máquinas da rede 10.5.2.*, então eu adiciono a seguinte linha no meu pg_hba.conf:

host all all 10.5.2.0 255.255.0.0 md5

Feito isso, basta reiniciar o postgres com o comando:

sudo /etc/init.d/postgresql-8.3 restart

Mais comandos:

Original em:

Os comandos do PostgreSQL são parecidos com os comandos do MySQL, só há algumas pequenas diferenças. Caso surja alguma dúvida ao ler este artigo: visite a documentação (ajuda) do PostgreSQL no site oficial.

Com o PostgreSQL já instalado em sua máquina, para acessá-lo digite o comando:

$ psql

Aparecerá a seguinte mensagem de ‘bem-vindo’:

Welcome to psql 7.4.2, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

Precisando de uma ajudazinha é só digitar: \h

Desta forma, aparecerá uma listagem dos comandos do PostgreSQL. Caso você queira ter mais detalhes sobre o comando ‘CREATE DATABASE’ digite:

\h CREATE DATABASE

OBS.: Não digite ‘\h CREATE DATABASE;’ (com ponto-e-vírgula ; no final) pois assim aparecerá uma mensagem de erro.

Criando, acessando e adicionando dados: banco de dados e tabela

Para criar um banco de dados, fazemos:

CREATE DATABASE nosso_banco_de_dados; // utiliza-se ponto-e-vírgula no final de cada comando SQL

Para acessar este banco de dados que acabamos de criar (único modo que sei fazer):

Saímos do PostgreSQL usando: \q

Retornamos ao PostgreSQL usando o comando:

psql nosso_banco_de_dados

Agora que estamos acessando nosso banco de dados, iremos criar uma simples tabela nele.

CREATE TABLE tabela_noticia (

titulo VARCHAR(90), — título da notícia

conteudo TEXT — texto da notícia

);

Alguns comentários:

‘CREATE TABLE’ é o comando utilizado para criar uma tabela.

‘tabela_noticia’ é o nome da tabela.

‘titulo’ é uma coluna da tabela_noticia e ‘VARCHAR(90)’ é o tipo de variável desta coluna (VARCHAR(X) = X caracteres).

‘conteudo’ outra coluna da tabela_noticia e ‘TEXT’ é o tipo da variável desta coluna.

Dois traços ‘–’ introduz um comentário, o PostgreSQL ignora o que vem depois deles.

Para saber mais sobre o comando ‘CREATE TABLE’ digite:

\h CREATE TABLE

Para inserir uma notícia com o título: ‘minha primeira noticia’ e com o conteúdo: ‘esta eh a minha primeira noticia’, devemos usar o seguinte comando:

INSERT INTO tabela_noticia (titulo, conteudo) VALUES (‘minha primeira noticia’, ‘esta eh a minha primeira noticia’);

Pronto, se tudo correu bem, sem nenhum erro, então temos um banco de dados, uma tabela e dados inseridos nesta tabela. Para vermos os dados salvos nesta tabela, ou seja, para lermos as notícias gravadas, digitamos o comando:

SELECT * FROM tabela_noticia;

Receberemos o texto de resposta:

titulo | conteudo

————————+———————————-

minha primeira noticia | esta eh a minha primeira noticia

(1 row)

Minha pequena ajuda acaba por aqui. Sim, só pra concluir, se não gostou desta tabela, para excluí-la faça:

DROP TABLE tabela_noticia;

e para apagar o BANCO DE DADOS (único modo que sei fazer):

Saia do PostgreSQL: \q

Volte ao PostgreSQL: psql

Depois digite:

DROP DATABASE nosso_banco_de_dados;

\q

Listar databases, schemas, colunas, conectar ao banco e listar colunas de uma tabela no Postgres

Este post vem informar como listar databases, schemas, tabelas, colunas de uma tabela do banco Postgree via terminal.
Primeiramente acesse o banco via terminal da seguinte maneira

root@server:~# su – postgres
postgres@server:~$ psql
Bem vindo ao psql 8.3.9, o terminal iterativo do PostgreSQL.

Digite: \copyright para mostrar termos de distribuição
\h para ajuda com comandos SQL
\? para ajuda com comandos do psql
\g ou terminar com ponto-e-vírgula para executar a consulta
\q para sair
postgres=#

Agora vamos listar as databases com o \L

postgres=# \l
Lista dos bancos de dados
Nome | Dono | Codificação
———————–+———-+————-
banco1 | nomedono | UTF8
banco2 | nomedono | UTF8

Conectar ao banco:
1)Sair do postgres com \q
2)psql -h [ip_host] [nome_banco] [dono]

postgres@server:~$ psql -h ???.com.br digitacao_???? usuario
Senha para usuário usuario:
Bem vindo ao psql 8.3.9, o terminal iterativo do PostgreSQL.

Digite: \copyright para mostrar termos de distribuição
\h para ajuda com comandos SQL
\? para ajuda com comandos do psql
\g ou terminar com ponto-e-vírgula para executar a consulta
\q para sair

conexão SSL (cifra: DHE-RSA-AES256-SHA, bits: 256)

digitacao_???=#

Ver Schemas do banco, tabelas e donos:
ainda conectado ao banco digite a query abaixo

QUERY
========
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

RESPOSTA
=========
esquema | tabela | dono
———-+—————–+——–
cadastro | cadastros | master
usuario | perfis | master
usuario | perfis_usuarios | master
usuario | usuarios | master
(4 registros)

E, para finalizar, como listar as colunas da tabela:
digitacao_???=# \d [schema].[tabela]

digitacao_???=# \d usuario.perfis
Tabela “usuario.perfis”
Coluna | Tipo | Modificadores
——–+————————+———————————————————-
id | integer | not null default nextval(‘usuario.seq_perfis’::regclass)
nome | character varying(100) | not null
Índices:
“perfis_pkey” PRIMARY KEY, btree (id)

digitacao_foxlux=# \d usuario.perfis
Tabela “usuario.perfis”
Coluna | Tipo | Modificadores
——–+————————+———————————————————-
id | integer | not null default nextval(‘usuario.seq_perfis’::regclass)
nome | character varying(100) | not null
Índices:
“perfis_pkey” PRIMARY KEY, btree (id)

PostgreSQL Prático/Metadados

12 – Metadados (Catálogo)

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 Tablespaces

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

Mostrar detalhes de uma function

SELECT 
 pc.oid AS prooid,
 proname,
 lanname as prolanguage,
 pg_catalog.format_type(prorettype, NULL) as proresult,
 prosrc,
 probin,
 proretset,
 proisstrict,
 provolatile,
 prosecdef,
 pg_catalog.oidvectortypes(pc.proargtypes) AS proarguments,
 proargnames AS proargnames,
 pg_catalog.obj_description(pc.oid, 'pg_proc') AS procomment
FROM pg_catalog.pg_proc pc, pg_catalog.pg_language pl
WHERE pc.oid = 'oid_da_function'::oid
AND pc.prolang = pl.oid

Este exemplo mostra uma consulta que lista os nomes dos esquemas, tabelas, colunas e chaves das chaves estrangeiras, e os nomes dos esquemas, tabelas e colunas referenciadas. Exemplo tirado da lista de discussão pgsql-sql

CREATE TEMPORARY TABLE t1 (id SERIAL PRIMARY KEY, nome TEXT);
CREATE TEMPORARY TABLE t2 (id INT REFERENCES t1, nome TEXT);
SELECT
 n.nspname AS esquema,
 cl.relname AS tabela,
 a.attname AS coluna,
 ct.conname AS chave,
 nf.nspname AS esquema_ref,
 clf.relname AS tabela_ref,
 af.attname AS coluna_ref,
 pg_get_constraintdef(ct.oid) AS criar_sql
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]);

Mostrar Esquemas e Tabelas

SELECT n.nspname as esquema, c.relname as tabela, a.attname as campo, format_type(t.oid, null) as tipo_de_dado
 FROM pg_namespace n, pg_class c, 
 pg_attribute a, pg_type t
 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
 and a.attnum > 0 -- no system att's
 and not a.attisdropped -- no dropped columns
 and a.attrelid = c.oid
 and a.atttypid = t.oid
 ORDER BY nspname, relname, attname;

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

Contar Todos os Registros de todas as tabelas de todos os bancos:

<?php
$conexao=pg_connect("host=127.0.0.1 user=postgres password=postabir");
$sql="SELECT datname AS banco FROM pg_database ORDER BY datname";
$consulta=pg_query($conexao,$sql);
$banco = array();
$c=0;
while ($data = @pg_fetch_object($consulta,$c)) {
 $cons=$data->banco; 
 $banco[] .= $cons;
 $c++;
}
$sql2="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";
for ($x=0; $x < count($banco);$x++){
 if ($banco[$x] !="template0" && $banco[$x] != "template1" && $banco[$x] !="postgres"){
 $conexao2=pg_connect("host=127.0.0.1 dbname=$banco[$x] user=postgres password=postabir");
 $consulta2=pg_query( $conexao2, $sql2 ); 
 while ($data = pg_fetch_object($consulta2)) {
 $esquematab=$data->esquema.'.'.$data->tabela;
 $sql3="SELECT count(*) FROM $esquematab";
 $consulta3=pg_query($conexao2,$sql3);
 $res=@pg_fetch_array($consulta3);
 print 'Banco.Esquema.Tabela -> 
 '.$banco[$x].'.'.$data->esquema.'.'.$data->tabela.' - Registro(s) - '.$res[0].'
';
 $total += $res[0];
 }
 }
}
print "Total de Registro de todas as tabelas de todos os bancos ". $total; 
?>

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;

Mostrar chaves primárias das tabelas do esquema public

select indexrelname as indice, relname as tabela from pg_catalog.pg_statio_user_indexes as A 
INNER JOIN pg_catalog.pg_index as B ON A.indexrelid=B.indexrelid WHERE A.schemaname='public' AND B.indisprimary = true;

Para visualizar como as consultas são feitas internamente via psql usamos o comando assim:

psql -U user banco -E

Vamos usar o banco municipios, criado com os municípios do Brasil. A tabela opt_cidades.

Veja Um Exemplo Que Retorna a Chave Primária da Tabela opt_cidades

SELECT ic.relname AS index_name, bc.relname AS tab_name, ta.attname AS column_name, i.indisunique AS unique_key, i.indisprimary AS primary_key

FROM pg_class bc, pg_class ic, pg_index i, pg_attribute ta, pg_attribute ia WHERE bc.oid = i.indrelid AND ic.oid = i.indexrelid AND ia.attrelid = i.indexrelid AND ta.attrelid = bc.oid AND bc.relname = ‘opt_cidades’ AND ta.attrelid = i.indrelid AND ta.attnum = i.indkey[ia.attnum-1] ORDER BY index_name, tab_name, column_name;

Retornará:

index_name | tab_name | column_name | unique_key | primary_key

opt_cidades_pkey | opt_cidades | id | t | t

Retornando o Nome do Esquema

 SELECT n.nspname AS "Esquema"
 FROM pg_catalog.pg_namespace AS n,
 pg_catalog.pg_class AS c
 WHERE c.relnamespace = n.oid AND c.relname='opt_cidades';

Retorno: Esquema

Retornar nomes de bancos:

SELECT datname AS banco FROM pg_database WHERE datname != ‘template0′ and datname != ‘template1′ and datname != ‘postgres’ ORDER BY datname

Retornar nomes e OIDs dos bancos:

SELECT oid, datname FROM pg_database;

Dado a tabela, qual o seu arquivo: select relname, relfilenode from pg_class;

No Windows

Podemos passar parâmetros para as macros, por exemplo:

doskey /exename=psql.exe dbinfo=SELECT datname,pg_encoding_to_char(encoding) FROM pg_database WHERE datname=’$1′;

E então apenas passar o parâmetro na linha de comando:

postgres=# dbinfo postgres

Listar tabelas, e dono do esquema atual:

SELECT n.nspname as “Schema”,

c.relname as “Tabela”,

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 “Tipo”,

u.usename as “Dono”

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;

Listar Tabelas

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);

SELECT tablename FROM pg_tables WHERE tablename NOT LIKE ‘pg%’ AND tablename NOT LIKE ‘sql\_%’

Listar todas as tabelas, índices, tamanho em KB e OIDs:

VACUUM; –Executar antes este comando

SELECT c1.relname AS tabela, c2.relname AS indice,

c2.relpages * 8 AS tamanho_kb, c2.relfilenode AS arquivo

FROM pg_class c1, pg_class c2, pg_index i

WHERE c1.oid = i.indrelid AND i.indexrelid = c2.oid

UNION

SELECT relname, NULL, relpages * 8, relfilenode

FROM pg_class

WHERE relkind = ‘r’

ORDER BY tabela, indice DESC, tamanho_kb;

Tabelas e Soma

SELECT tablename, SUM( size_kb )

FROM

( SELECT c1.relname AS “tablename”,

c2.relpages * 8 AS “size_kb”

FROM pg_class c1, pg_class c2, pg_index i

WHERE c1.oid = i.indrelid

AND i.indexrelid = c2.oid

UNION

SELECT relname, relpages * 8

FROM pg_class

WHERE relkind = ‘r’ ) AS relations

GROUP BY tablename;

– r = ordinary table, i = index, S = sequence, v = view, c = composite type, — s = special, t = TOAST table

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

Nomes e colunas:

select tablename,’T’ from pg_tables where tablename not like ‘pg\_%’ and tablename not in (‘sql_features’, ‘sql_implementation_info’, ‘sql_languages’, ‘sql_packages’, ‘sql_sizing’, ‘sql_sizing_profiles’)

union

select viewname,’V’ from pg_views where viewname not like ‘pg\_%’

Tamanho de esquema e índice:

SELECT nspname,

sum(relpages * cast( 8192 AS bigint )) as “table size”,

sum( ( select sum(relpages)

 from pg_class i, pg_index idx
 where i.oid = idx.indexrelid
 and t.oid=idx.indrelid ) ) * cast( 8192 AS bigint ) as "index size",

sum ( relpages * cast( 8192 AS bigint ) + ( select sum(relpages)

 from pg_class i, pg_index idx
 where i.oid = idx.indexrelid
 and t.oid=idx.indrelid ) * cast( 8192 AS bigint ) ) as "size"

FROM pg_class t, pg_namespace

WHERE relnamespace = pg_namespace.oid

and pg_namespace.nspname not like ‘pg_%’

and pg_namespace.nspname != ‘information_schema’

and relkind = ‘r’ group by nspname;

Retornando Tabelas e Seus Donos de um Esquema

SELECT n.nspname as “public”,

 c.relname as "opt_cidades",
 CASE c.relkind WHEN 'r' THEN 'tabela' WHEN 'v' THEN 'view' WHEN 'i' THEN 'índice' WHEN 'S' THEN

‘sequencia’ WHEN ‘s’ THEN ‘especial’ END as “Tipo”, u.usename as “Dono”

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;

Retorno:

public | opt_cidades | Tipo | Dono

+——————+——–+———-

public | opt_cidades | tabela | postgres
public | opt_estado | tabela | postgres

Retornando o OID e o Esquema de uma Tabela

SELECT c.oid AS “OID”,

 n.nspname AS "Esquema",
 c.relname AS "Tabela"

FROM pg_catalog.pg_class c

 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace

WHERE pg_catalog.pg_table_is_visible(c.oid)

 AND c.relname ~ '^opt_cidades$'

ORDER BY 2, 3;

Retorno:

 OID | Esquema | Tabela

Este exemplo mostra uma consulta que lista os esquemas, nomes das tabelas e nomes das colunas das chaves primárias de um banco de dados. Exemplo tirado da lista de discussão pgsql-sql .

CREATE TEMP TABLE teste1 (id INT, texto TEXT, PRIMARY KEY (id));

CREATE TEMP TABLE teste2 (id1 INT, id2 INT, texto TEXT, PRIMARY KEY (id1,id2));

\dt

SELECT

 pg_namespace.nspname AS esquema,
 pg_class.relname AS tabela,
 pg_attribute.attname AS coluna_pk
FROM pg_class
 JOIN pg_namespace ON pg_namespace.oid=pg_class.relnamespace AND
 pg_namespace.nspname NOT LIKE 'pg_%'
 JOIN pg_attribute ON pg_attribute.attrelid=pg_class.oid AND
 pg_attribute.attisdropped='f'
 JOIN pg_index ON pg_index.indrelid=pg_class.oid AND
 pg_index.indisprimary='t' AND
 (
 pg_index.indkey[0]=pg_attribute.attnum OR
 pg_index.indkey[1]=pg_attribute.attnum OR
 pg_index.indkey[2]=pg_attribute.attnum OR
 pg_index.indkey[3]=pg_attribute.attnum OR
 pg_index.indkey[4]=pg_attribute.attnum OR
 pg_index.indkey[5]=pg_attribute.attnum OR
 pg_index.indkey[6]=pg_attribute.attnum OR
 pg_index.indkey[7]=pg_attribute.attnum OR
 pg_index.indkey[8]=pg_attribute.attnum OR
 pg_index.indkey[9]=pg_attribute.attnum
 )
ORDER BY pg_namespace.nspname, pg_class.relname,pg_attribute.attname;

Este exemplo mostra uma consulta que lista os nomes dos esquemas, tabelas, colunas e chaves das chaves estrangeiras, e os nomes dos esquemas, tabelas e colunas referenciadas. Exemplo tirado da lista de discussão pgsql-sql

CREATE TEMPORARY TABLE t1 (id SERIAL PRIMARY KEY, nome TEXT);
CREATE TEMPORARY TABLE t2 (id INT REFERENCES t1, nome TEXT);
SELECT
 n.nspname AS esquema,
 cl.relname AS tabela,
 a.attname AS coluna,
 ct.conname AS chave,
 nf.nspname AS esquema_ref,
 clf.relname AS tabela_ref,
 af.attname AS coluna_ref,
 pg_get_constraintdef(ct.oid) AS criar_sql
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]);

Retorno:

 esquema | tabela | coluna | chave | esquema_ref | tabela_ref | coluna_ref |

criar_sql

pg_temp_1 | t2 | id | t2_id_fkey | pg_temp_1 | t1 | id | FOREIGN KEY (id)

REFERENCES t1(id)

SELECT a.attnum, a.attname AS field, t.typname as type, a.attlen AS length, a.atttypmod-4 as lengthvar, a.attnotnull as notnull

 FROM pg_class c, pg_attribute a, pg_type t
 WHERE c.relname = 'apagar' AND a.attnum > 0 AND a.attrelid = c.oid AND a.atttypid = t.oid
 ORDER BY a.attnum;

Saída:

ID do campo, nomecampo, tipo, tamanho, nulo/nãonulo

Outros

SELECT ic.relname AS index_name, bc.relname AS tab_name, ta.attname AS column_name, i.indisunique AS unique_key, i.indisprimary AS primary_key

 FROM pg_class bc, pg_class ic, pg_index i, pg_attribute ta, pg_attribute ia
 WHERE (bc.oid = i.indrelid)
 AND (ic.oid = i.indexrelid)
 AND (ia.attrelid = i.indexrelid)
 AND (ta.attrelid = bc.oid)
 AND (bc.relname = 'apagar')
 AND (ta.attrelid = i.indrelid)
 AND (ta.attnum = i.indkey[ia.attnum-1])
 ORDER BY index_name, tab_name, column_name

Saída:

nomeindex/chave, nometabela, nomecampo, unique(t/f), nomepk (t/f)

SELECT rcname as index_name, rcsrc

 FROM pg_relcheck, pg_class bc
 WHERE rcrelid = bc.oid
 AND bc.relname = 'apagar'
 AND NOT EXISTS (
 SELECT *
 FROM pg_relcheck as c, pg_inherits as i
 WHERE i.inhrelid = pg_relcheck.rcrelid
 AND c.rcname = pg_relcheck.rcname
 AND c.rcsrc = pg_relcheck.rcsrc
 AND c.rcrelid = i.inhparent
 )

Saída: retorna as constraints check.

SELECT pg_class.relname, pg_attribute.attname, pg_type.typname, pg_attribute.atttypmod-4 
 FROM pg_class, pg_attribute, pg_type 
 WHERE pg_attribute.attrelid = pg_class.oid 
 AND pg_attribute.atttypid = pg_type.oid 
 AND pg_class.relname = 'apagar' 
 AND pg_attribute.attname = 'descricao'

Saída: tabela, campo, tipo, tamanho (varchar)

Outros Exemplos

create table tabela_exemplo (

campo_1 integer default 5, campo_2 text default ‘exemplo’, campo_3 float(10),

campo_4 serial, campo_5 double precision, campo_6 int8, campo_7 Point,

campo_8 char(3), campo_9 varchar(17) );

Depois de criada a tabela vamos criar a consulta que nos retornará as informações da tabela:

SELECT

rel.nspname AS Esquema, rel.relname AS Tabela, attrs.attname AS Campo, “Type”, “Default”, attrs.attnotnull AS “NOT NULL”

FROM (

SELECT c.oid, n.nspname, c.relname

FROM pg_catalog.pg_class c

LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace

WHERE pg_catalog.pg_table_is_visible(c.oid) ) rel

JOIN (

SELECT a.attname, a.attrelid, pg_catalog.format_type(a.atttypid, a.atttypmod) as “Type”,

(SELECT substring(d.adsrc for 128) FROM pg_catalog.pg_attrdef d

WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef) as “Default”, a.attnotnull, a.attnum

FROM pg_catalog.pg_attribute a WHERE a.attnum > 0 AND NOT a.attisdropped ) attrs ON (attrs.attrelid = rel.oid ) WHERE relname = ‘tabela_exemplo’ ORDER BY attrs.attnum;

Retorno: testes-# WHERE relname = ‘tabela_exemplo’ ORDER BY attrs.attnum;

esquema | tabela | campo | Type | Default | NOT NULL

Antes de tudo devemos criar um novo tipo de dado relacionado ao retorno que obteremos da função:

CREATE TYPE tabela_estrutura AS (Esquema text, Tabela text, Campo text, Tipo text, Valor text, AutoIncremento bool);

A função abaixo é definida em PL/PgSQL, linguagem procedural muito semelhante ao PL/SQL do Oracle. A função foi criada nesta linguagem devido a certas limitações que as funções em SQL possuem.

CREATE OR REPLACE FUNCTION Dados_Tabela(varchar(30))
 RETURNS SETOF tabela_estrutura AS '
DECLARE
 r tabela_estrutura%ROWTYPE;
 rec RECORD;
 vTabela alias for $1;
 eSql TEXT;
BEGIN
 eSql := SELECT
 CAST(rel.nspname as TEXT), CAST(rel.relname AS TEXT) , CAST(attrs.attname AS TEXT), 
 CAST("Type" AS TEXT), CAST("Default" AS TEXT), attrs.attnotnull
 FROM
 (SELECT c.oid, n.nspname, c.relname
 FROM pg_catalog.pg_class c
 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
 WHERE pg_catalog.pg_table_is_visible(c.oid) ) rel
 JOIN 
 (SELECT a.attname, a.attrelid,
 pg_catalog.format_type(a.atttypid, a.atttypmod) as "Type",
 (SELECT substring(d.adsrc for 128) FROM pg_catalog.pg_attrdef d
 WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef)
 as "Default", a.attnotnull, a.attnum
 FROM pg_catalog.pg_attribute a
 WHERE a.attnum > 0 AND NOT a.attisdropped ) attrs
 ON (attrs.attrelid = rel.oid )
 WHERE relname LIKE '% || vTabela || %'
 ORDER BY attrs.attnum;
 FOR r IN EXECUTE eSql
LOOP
RETURN NEXT r;
END LOOP;
IF NOT FOUND THEN
 RAISE EXCEPTION Tabela % não encontrada, vTabela;
END IF;
RETURN;
END
' 
LANGUAGE 'plpgsql';

Para utilizar esta função, utilize o seguinte comando:

SELECT * FROM Dados_Tabela('tabela');

Retorno:

Exemplos contidos no arquivo:

/usr/local/src/postgresql-8.1.3/src/tutorial/syscat.sql

SELECT rolname as "Donos", datname as Bancos 
 FROM pg_roles, pg_database
 WHERE pg_roles.oid = datdba
 ORDER BY rolname, datname;

Retorno: Donos e Bancos

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

Retorno: Esquemas e Tabelas

SELECT n.nspname as esquema, c.relname as tabela, a.attname as campo, format_type(t.oid, null) as tipo_de_dado
 FROM pg_namespace n, pg_class c, 
 pg_attribute a, pg_type t
 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
 and a.attnum > 0 -- no system att's
 and not a.attisdropped -- no dropped columns
 and a.attrelid = c.oid
 and a.atttypid = t.oid
 ORDER BY nspname, relname, attname;

Retorno: esquemas, tabelas, campos, tipos de dados

SELECT n.nspname, o.oprname AS binary_op,
 format_type(left_type.oid, null) AS left_opr,
 format_type(right_type.oid, null) AS right_opr,
 format_type(result.oid, null) AS return_type
 FROM pg_namespace n, pg_operator o, pg_type left_type, 
 pg_type right_type, pg_type result
 WHERE o.oprnamespace = n.oid
 and o.oprkind = 'b' -- binary
 and o.oprleft = left_type.oid
 and o.oprright = right_type.oid
 and o.oprresult = result.oid
 ORDER BY nspname, left_opr, right_opr;

Retorno: operadores binários

Baypassar os de sistema:

and n.nspname not like ‘pg\\_%’ — no catalogs

SELECT n.nspname, p.proname, format_type(t.oid, null) as typname
 FROM pg_namespace n, pg_aggregate a, 
 pg_proc p, pg_type t
 WHERE p.pronamespace = n.oid
 and a.aggfnoid = p.oid
 and p.proargtypes[0] = t.oid
 ORDER BY nspname, proname, typname;

Retorno: lista todas as funções agregadas e os tipos que podem ser aplicados

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;

Exemplo que retorna índice, campo, tipo, comprimento, null, default:

 SELECT pg_attribute.attnum AS index, 
 attname AS field, 
 typname AS type, 
 atttypmod-4 as length, 
 NOT attnotnull AS "null", 
 adsrc AS default 
 FROM pg_attribute, 
 pg_class, 
 pg_type, 
 pg_attrdef 
 WHERE pg_class.oid=attrelid 
 AND pg_type.oid=atttypid 
 AND attnum >0 
 AND pg_class.oid=adrelid 
 AND adnum=attnum 
 AND atthasdef='t' 
 AND lower(relname)='datas' 
 UNION 
 SELECT pg_attribute.attnum AS index, 
 attname AS field, 
 typname AS type, 
 atttypmod-4 as length, 
 NOT attnotnull AS "null", 
 AS default 
 FROM pg_attribute, 
 pg_class, 
 pg_type 
 WHERE pg_class.oid=attrelid 
 AND pg_type.oid=atttypid 
 AND attnum>0 
 AND atthasdef='f' 
 AND lower(relname)='datas';

Categoria: PostgreSQL Prático

Comments (0)

Postgres – Instalação no Línux

Instalação

usuário – CRIAR UM NOVO

Instalando e configurando o PostgreSQL 8.3 no Ubuntu

Mais comandos:

Criando, acessando e adicionando dados: banco de dados e tabela

Listar databases, schemas, colunas, conectar ao banco e listar colunas de uma tabela no Postgres

PostgreSQL Prático/Metadados

12 – Metadados (Catálogo)

No comments yet.