Trabalhando com SQL
Quase todas estas dicas abaixo recebi na lista de PostgreSQL do http://postgresql.org.br. Sempre que lembrei concedi os devidos créditos.
1) Criar Tabela tendo outra outra como base e já importando todos os registros dessa outra:
CREATE TABLE tabelanova AS SELECT * FROM tabealexistente;
2) Inserindo com SELECT
Inserir todos os registros de uma tabela em outra:
INSERT INTO tabelaqueimporta SELECT * from tabelaqueexporta;
insert into engenharia.insumos (grupo,insumo,descricao,unidade) select grupo,insumo,descricao, CAST(unidade AS int2) AS “unidade” from engenharia.apagar
insert into engenharia.insumos (grupo,insumo,descricao,unidade) select grupo,insumo,descricao, cast(unidade AS INT2) AS unidade from engenharia.apagar
$conn = pg_connect(”host=10.40.100.186 dbname=apoena user=_postgresql”);
for($x=10;$x<=87;$x++){
$sql=”update engenharia.precos set custo_produtivo = (select custo_produtivo from engenharia.apagar where insumo=’$x’) where insumo=’00′ || ‘$x’”;
$ret=pg_query($conn,$sql);
}
3) Atualizar um campo em todos os registros de uma tabela recebendo de outra tabela:
UPDATE servicos s SET custo = total FROM composicoes c
WHERE s.tabela = c.tabela AND s.servico = c.servico
Uso do Like e de Expressões Regulares
Registros:
Ribamar Ferreira de Sousa
João Pereira Brito
Usando LIKE e ILIKE
SELECT * FROM clientes WHERE nome LIKE ‘Riba%’; // Retorna Ribamar Ferreira de Sousa
SELECT * FROM clientes WHERE nome LIKE ‘riba%’; // Nada retorna
SELECT * FROM clientes WHERE nome ILIKE ‘riba%’; // Retorna Ribamar Ferreira de Sousa
SELECT * FROM clientes WHERE nome NOT LIKE ‘pedro’; // Retorna ambos os registros
Usando Expressões Regulares
SELECT * FROM clientes WHERE nome ~~ ‘Riba%’; // Retorna Ribamar Ferreira de Sousa
SELECT * FROM clientes WHERE nome ~~ ‘riba%’; // Nada retorna
SELECT * FROM clientes WHERE nome ~~* ‘riba%’; // Retorna Ribamar Ferreira de Sousa
SELECT * FROM clientes WHERE nome !~~ ‘pedro’; // Retorna ambos os registros
SELECT nome FROM clientes WHERE nome ~ ‘Ribamar Ferreira de Sousa’; // Retorna Ribamar Ferreira de Sousa
SELECT * FROM clientes WHERE nome !~ ‘jorge’; // Retorna ambos
4) Buscar nas tabelas de sistema do postgresql, todos as tabelas de um determinado schema, os campos que sejam do tipo boolean..
SELECT n.nspname AS Schema, c.relname AS Tabela, t.typname AS Tipo
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_type t ON t.oid = c.reltype
WHERE c.relkind = ‘r’::”char”
AND t.typname = ‘boolean’;
5) Exemplos de Joins
Join com 4 tabelas
$w_sql = ” TRUE “;
if ( $p_tabela != “”) { $w_sql = $w_sql . ” AND tabela ~~*’” . $p_tabela . “‘”; }
if ( $p_insumo_grupo != “”) { $w_sql = $w_sql . ” AND insumo_grupo ~~*’” .$p_insumo_grupo.”‘”; }
if ( $p_insumo != “”) { $w_sql = $w_sql . ” AND insumo ~~*’” . $p_insumo . “‘”; }
if ( $p_fornecedor != “”) { $w_sql = $w_sql . ” AND fornecedor ~~*’” .$p_fornecedor.”‘”; }
$w_sql=”SELECT distinct on (p.tabela, p.insumo_grupo, p.insumo, p.fornecedor) p.custo_produtivo, p.data_inclusao,
t.tabela, t.descricao as tabelad,
ig.grupo, ig.descricao as insumogd,
i.grupo, i.insumo, i.descricao as insumod,
f.codigo_fornecedor, f.razao_social as fornecedord
FROM $m_table as p, $m_table_tab as t, $m_table_ing as ig, $m_table_ins as i, $m_table_for as f
WHERE p.tabela=t.tabela AND p.insumo_grupo=ig.grupo AND p.insumo=i.insumo AND p.fornecedor=f.codigo_fornecedor
AND p.insumo_grupo = i.grupo ORDER BY p.tabela DESC, p.insumo_grupo;”;
/*
p – $m_table (engenharia.precos)
i – $m_table_ins (engenharia.insumos)
ig – $m_table_ing (engenharia.insumos_grupos)
t – $m_table_tab (engenharia.tabela)
*/
6) Mudar Tipo de Dados de Campo – CAST (Só >=8.0):
ALTER TABLE tabela ALTER COLUMN campo TYPE tipo;
ALTER TABLE produtos ALTER COLUMN preco TYPE numeric(10,2);
ALTER TABLE produtos ALTER COLUMN data TYPE DATE USING CAST (data AS DATE);
7) Renomear Tabela
ALTER TABLE tabela RENAME TO nomenovo;
ALTER TABLE produtos RENAME TO equipamentos;
Tamanho de Tabela, Banco ou Todos os Bancos do SGBD:
Tamanho de Banco de Dados (postgresql 8.1 ou superior):
select pg_database_size(’nomebanco’);
Tamanho de Tabela
select pg_tablespace_size(’nometabela’);
Tamanho de todos os bancos de dados do SGBD:
select (sum(relpages) * / 1024 || ‘ MB’ as tamanho from pg_class where relowner > 1;
Ou
select (sum(relpages) / 2^7) :: int || ‘ MB’ as tamanho from pg_class where relowner > 1;
9) Validação de e-mails
1 – select distinct(campo_email),campo_nome, campos_n from tabela where campo_email like ‘%@%.%’
2 – SELECT POSITION(’@', ‘ribafs@gmail.com’) > 0
3 – select ‘coutinho.php@gmail.com’ ~ ‘@’
4 – select ‘coutinho.php@gmail.com’ like ‘%@%’
5 – select if (’campo_email’ like “%@%.%”,”TRUE”,”FALSE”) as flag, campo_adcional from tabela
6 – select ‘coutinho@gmail.com’ similar to ‘%@%.%’;
10) Temos um campo (insumo) com valores = 1, 2, 3, … 87
Queremos atualizar para 0001, 0002, 0003, … 0087
UPDATE equipamentos SET insumo = ‘000′ || insumo WHERE LENGTH(insumo) = 1;
UPDATE equipamentos SET insumo = ‘00′ || insumo WHERE LENGTH(insumo) = 2;
Outra saída mais elegante ainda:
UPDATE equipamentos SET insumo = REPEAT(’0′, 4-LENGTH(insumo)) || insumo;
11) Retornar o número de usuários conectados
select count(*) from pg_stat_activity
pg_stat_database que apresenta para cada banco de dados o número de conexões.
Eu particularmente acho que fica mais fácil de visualizar do que o pg_stat_activity quando se tem muitas conexões.
Mostrar uso dos índices dos bancos de dados:
select * from pg_statio_user_indexes;
select * from pg_stat_user_indexes;
Mostra estatística de uso das tabelas e manutenção:
select * from pg_stat_all_tables;
Mostra todas as tabelas do atual esquema do atual banco:
select * from pg_stat_user_tables;
pg_stat_get_tuples_returned(oid) bigint Number of rows read by sequential scans when argument is a table, or number of index entries returned when argument is an index
pg_stat_get_tuples_fetched(oid) bigint Number of table rows fetched by bitmap scans when argument is a table, or table rows fetched by simple index scans using the index when argument is an index
pg_stat_get_tuples_inserted(oid) bigint Number of rows inserted into table
pg_stat_get_tuples_updated(oid) bigint Number of rows updated in table
pg_stat_get_tuples_deleted(oid) bigint Number of rows deleted from table
pg_stat_get_blocks_fetched(oid) bigint Number of disk block fetch requests for table or index
pg_stat_get_blocks_hit(oid) bigint Number of disk block requests found in cache for table or index
pg_stat_get_last_vacuum_time(oid) timestamptz Time of the last vacuum initiated by the user on this table
pg_stat_get_last_autovacuum_time(oid) timestamptz Time of the last vacuum initiated by the autovacuum daemon on this table
pg_stat_get_last_analyze_time(oid) timestamptz Time of the last analyze initiated by the user on this table
pg_stat_get_last_autoanalyze_time(oid) timestamptz Time of the last analyze initiated by the autovacuum daemon on this table
This is controlled by configuration parameters that are normally set in postgresql.conf
The function pg_stat_get_backend_idset provides a convenient way to generate one row for each active server process. For example, to show the PIDs and current queries of all server processes:
SELECT pg_stat_get_backend_pid(s.backendid) AS procpid,
pg_stat_get_backend_activity(s.backendid) AS current_query
FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s;
Visualizar os processos do portgresql num UNIX:
ps auxww | grep ^postgres
Formato de retorno:
postgres: user database host activity
12) Corrigindo Estouro do Máximo de transações (2 bilhões)
Constatando:
SELECT datname, age(datfrozenxid) FROM pg_database;
age acusa mais de 2 bilhões
Tarcizio Meurer
- Execute um dumpall na base
- drop a base e o agrupamento de dados
- recrie o agrupamento
- recrie a base
- carrege os dados novemente.
13) Total de Registros de Todos os Bancos do SGBD (PHP):
<?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;
?>
14) Uso da Constraint check
CREATE TABLE testes(
codigo serial primary key,
idade integer,
check (idade > 18 AND idade < 70)
)
Alternativas:
check (preco > desconto)
check (desconto > 0 AND preco > desconto)
————-
Somente aceitar c ou e (simulando campo tipo enum do MySQL):
tipo char(1) check (tipo =’c’ OR tipo=’e')
Para este cria-se uma combo com values ‘c’ e ‘e’.
15) Manutenção do PostgreSQL:
No CRON:
/home/pgsql/bin/psql -c “vacuum full analyse” -d dadosadv -U postgres
Consultas no Pronpt do SO:
psql -U postgres -d banco -c “SELECT * FROM clientes”
Manutenção em Tabela
vacuum analize tabela;
Reindexar Banco, tabela ou índice
reindex database banco;
Exibir plano de consulta
explain select * from tabela;
Exibir todos os parâmetros de runtime
show all;
16) Consulta com Dias Úteis
Só para constar aqui vai uma expressão SQL que fornece os
dias úteis de um período. Considerei que existe uma tabela
com o registro dos feriados e outros dias que não devem ser
considerados (emendas, pontos facultativos, etc):
SELECT dia FROM
(SELECT (’2007-10-01′::date+s.a*’1 day’::interval) AS dia
FROM generate_series(0, ‘2007-10-31′::date -
‘2007-10-01′::date, 1) AS s(a)) foo
WHERE EXTRACT(DOW FROM dia) BETWEEN 1 AND 5
EXCEPT
SELECT dia FROM tab_feriado;
Osvaldo (na lista postgresql-br)
17) Update em uma chave primária sem causar duplicação de chave
UPDATE teste SET coluna1 = t_aux.coluna1+1
FROM (
SELECT coluna1
FROM teste
ORDER BY coluna1 DESC
) t_aux
WHERE teste.coluna1 = t_aux.coluna1;
Osvaldo (na lista postgresql-br)
18) Como saber se existe uma transação ativa
select pg_stat_activity;
Dica do João Paulo.
19)Inserir data como valor default:
Pode usar também o current_date ou o localtimestamp.
insert into tabela(data) values ((select current_date));
ou
insert into tabela(data) values ((select localtimestampo));
20) Ler último saldo de tabela
Tenho o seguinte conteudo numa tabela de contas:
Lancto–CCorrente–Banco–OP–DataLan——-Valor———Saldo–
1 12345-6 002 C 19/11/2007 1000.00 1000.00
2 12345-6 002 C 19/11/2007 2000.00 3000.00
3 12345-6 002 D 19/11/2007 100.00 2900.00
4 23450-6 001 C 19/11/2007 2000.00 3000.00
5 23450-6 001 D 19/11/2007 100.00 2900.00
Preciso retornar sempre o último SALDO registrado.
Como nunca vou saber a data exata da periodo de consulta.
Estou executando:
SELECT saldoatual FROM lanban WHERE contacorrente = ‘12345-6′ and datalan <= ‘2007/12/01′ ORDER BY datalan DESC LIMIT 1
Retona o Saldo: 1000.00, preciso pegar o ultimo saldo da conta 12345-6: que é 2900.00.
Isso porque tabelas são conjuntos de dados. O padrão SQL *não* garante a
ordem dos dados. Mesmo se ele garantisse, um simples UPDATE podia mudar
o ordem dos dados e o seu SELECT não retornaria o valor desejado.
> Alguem tem alguma dica?
>
O campo ‘Lancto’ é do tipo serial? Se for poderias utilizar:
SELECT saldoatual FROM lanban WHERE contacorrente = ‘12345-6′ ORDER BY
“Lancto” DESC LIMIT 1.
Dica do Euler Taveira de Oliveira
21) Formato de moeda
O correto seria:
to_char(1030.52,’9G999D99′)
mas o resultado é: 1,030,52
como você pode observar existe um problema no
separador de milhar (indicado pelo G) que é
considerado como , e não como . que seria o esperado.
Uma maneira de contornar (não muito elegante) é:
to_char(1030.52,’9″.”999D99′)
Corrigido na versão 8.3
22) Saber o Tamanho de Tabela e de Índices
pg_relation_size()
pg_total_relation_size()
-Leo
–
Leonardo Cezar
23) Último Saldo
Fernando Brombatti
A situação é a seguinte. Não se sabe se o serial citado (por N razões) vai ser o último valor existente. Nada me garante que estes dados não sofreram algum UPDATE. Sendo assim, recomendo:
1) alterar o campo DATE para TIMESTAMP
2) alterar o query:
SELECT lan.saldoatual
FROM lanban lan
WHERE lan.contacorrente = ‘12345-6′ AND lan.datalan = (SELECT MAX(maxlan.datalan)
FROM lanban maxlan
WHERE maxlan.contacorrente = lan.contacorrente)
Isso faz com que no primeiro SQL eu traga os lancamentos da conta e no segundo eu trago a máxima data de lançamento para a mesma conta. Como as contas são iguais, trago a máxima data da conta atual, logo tenho o saldo atual.
É confuso, mas é o mais seguro (podem haver UPDATES neste caso também, mas aí não se depende de um serial).
Para este query funcionar bem necessita mais um índice em datalan ao menos.
Nos nossos sistemas da prefeitura nunca usamos saldos desta forma, pois aí se é removido algum registro a informação não fica correta.
Espero não ter confundido tanto.
24) Encontrando tanela de sistema
Para localizar informações desse tipo existe o information_schema
(conforme citado pelo Leandro). Utilizando o catalogo poupa voce de
futuras dores de cabeça quando por exemplo houver alguma alteração
estrutural em tabelas do sistema em versõs futuras. As views do
catalogo deverão permanecer com o máximo de compatibilidade entre
versões (segundo padrão SQL).
Além de ser mais simples:
SELECT *
FROM information_schema.tables
WHERE table_name = ‘foobar’;
Infelizmente não possuimos referencias a outros banco de dados
(banco.schema.tabela), portanto o comando deverá ser executado em
todos seus bancos para localizar a tabela ou um programeta bash
parecido com isso:
$ ARG=$1 || “foo” && for DATABASE in `psql -U postgres -c “\l” \
| cut -d”|” -f1 | egrep ‘^(\ [a-z])’`
do
psql -U postgres -d $DATABASE -Atc \
“SELECT ‘O banco de dados $DATABASE possui a tabela: $RG’
FROM information_schema.tables
WHERE table_name = ‘$ARG’”;
done;
Abraço!
-Leo
25) Como Localizar e Deletar registros duplicados
1.Select para localizar duplicados
select campo,campo1,count(*)
from tabela having count(*) > 1
group by campo,campo1
2.Deletar duplicados:
delete from tab p1
where rowid < (select max(rowid)
from tab1 p2
where p1.primary_key = p2.primary_key);
26) Inserir registros em uma específica posição
> Hi, how are you? maybe you know how SQL insert data
> bellow or above in database tabe? example insert
> data from position table 5 thanks
>
No, I don’t known.
But if you make a copy from table,
create a new table with same structure,
insert a new register,
import register from old table, then first register
are this last register inserted.
27) Timezones do PostgreSQL (lista pgbr-geral)
No POSTGRESQL.CONF tem o timezone onde você pode colocar algo do tipo:
TIMEZONE=BRAZIL/EAST esta é minha configuração, ou seja, de minha região.
Analise.
Wandrey
Outra ———–
Na maioria dos casos é criado um link do diretório de timezones do
S.O. (/usr/share/zoneinfo//usr/share/zoneinfo/) para o diretório de
Timezones do Postgres ($PGDIR/share/timezone )Que possui seu próprio
sistema de controle de timezone, se não me engano a partir d versão
–
Att:
Thiago Risso
28) Inserir Número Aleatório em Tabela
CREATE TABLE page (
id SERIAL PRIMARY KEY,
about TEXT NULL,
);
ALTER TABLE page ADD myrand NUMERIC NOT NULL DEFAULT RANDOM();
UPDATE page SET myrand = DEFAULT;
SELECT id FROM page WHERE myrand >= RANDOM() ORDER BY myrand LIMIT 1;
This approach has some problems:
* If the number you pick is greater than the largest number in the myrand column, you will not find any matching rows.
* The gaps between the random values in the myrand column are not uniform, and thus the rows selected are not random. Imagine a table with two rows and myrand values of 0.8 and 0.9. If the random number compared to myrand is .8 or less, the first row is chosen. But the second row is only chosen if the value picked is between .8 and .9
* If more than one row has the exact same number, it is likely that one of them will never get picked.
Mais detalhes em: http://people.planetpostgresql.org/greg/index.php?/archives/118-guid.htm…
29) Desabilitar Triggers
Vinicius Santos – MSI escreveu:
Thiago Boufleuhr escreveu:
Como faço para desabilitar as triggers em uma sessão no PLSQL ?
Thiago Boufleuhr
ALTER TABLE [NOME DA TABELA]
DISABLE TRIGGER [NOME DA TRIGGER]
Ou
ALTER TABLE [NOME DA TABELA]
DISABLE TRIGGER ALL
ALERTA:
William Leite Araújo: MUITO CUIDADO AO USAR “DISABLE TRIGGER ALL”
As constraints de chave estrangeira são controladas via TRIGGER. Caso desabilite todos os gatilhos, a checagem da integridade referencial (chaves estrangeiras) serão desabilitadas!
30) Codificação de Caracteres
Euler Taveira de Oliveira
>Evandro Ricardo Silvestre wrote: Codificação de caracteres do cliente e
do servidor podem ser diferentes. Se a codificação do cliente é diferente da codificação do servidor, o servidor PostgreSQL tenta fazer uma conversão antes de armazenar/retornar os dados. Um problema que existia é que a aplicação cliente (no caso abaixo o psql) não avisava se a codificação informada ao servidor (client_encoding) era a mesma do ambiente (terminal).
Bem vindo ao psql 8.3.0, 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
template1=# show client_encoding;
client_encoding
—————–
LATIN1
(1 registro)
template1=# show server_encoding;
server_encoding
—————–
LATIN1
(1 registro)
template1=# select upper(’áéíóú’);
upper
——-
ÁÉÍÓÚ
(1 registro)
template1=# set client_encoding to ‘utf-8′;
SET
template1=# show client_encoding;
client_encoding
—————–
utf-8
(1 registro)
template1=# select upper(’áéíóú’);
ERRO: sequência de bytes é inválida para codificação “UTF8″: 0xe1e9ed
DICA: Este erro pode acontecer também se a sequência de bytes nãocorresponde a codificação esperado pelo servidor, que é controlada por “client_encoding”.
ERRO: sequência de bytes é inválida para codificação “UTF8″: 0xe1e9ed
DICA: Este erro pode acontecer também se a sequência de bytes não corresponde a codificação esperado pelo servidor, que é controlada por “client_encoding”.
[trocando a codificação de caracteres do terminal e digitando novamente]
template1=# select upper(’áéí’);
upper
——-
ÁÉÍ
(1 registro)
31) Como visualizar as consultas correntes no Postgres
Colaboração: Frederico Palma
Data de Publicação: 16 de fevereiro de 2008
É necessário habilitar o stats_command_string no postgresql.conf:
stats_command_string = true
Essa configuração pode ser alterada em um banco que está ativo sem a necessidade de reiniciá-lo e sem afetar as conexões abertas para recarregar as configurações. Envie um SIGHUP ou use o comando:
pg_ctl reload
Quando stats_command_string está ativo a tabela pg_stat_activity armazena todas consultas correntes.
Realizando a consulta:
SELECT datname,procpid,current_query FROM pg_stat_activity
Teremos a lista dos bancos de dados utilizados com seus respectivos processos (PID) referente às consultas.
SELECT datname,procpid,current_query FROM pg_stat_activity ORDER BY procpid;
datname | procpid | current_query
————+———+—————–
mydatabase1 | 2587 | < IDLE>
mydatabase2 | 15726 | SELECT * FROM users WHERE id=123 ;
mydatabase3 | 15851 | < IDLE>
Publicado originalmente na Dicas-L – http://www.dicas-l.com.br/dicas-l/20080216.php
32)
