PostgreSQL Prático/Apêndices/Dicas Práticas de uso do SQL
15.4 - Dicas Práticas de uso do SQL
editarArmazenar Arquivos Binários no Próprio Banco
Utilize a contrib LO para esta finalidade.
Lembre que como é uma contrib normalmente não vem ligada e temos que ligar especificamente ao banco onde queremos utilizar.
Ligando, de dentro do banco usar o comando \i:
Acesse o diretório lo das contribs do PostgreSQL:
/usr/local/src/postgresql-8.1.3/contrib/lo
Então execute o comando "make install".
Acesse o banco e:
\i /usr/local/src/postgresql-8.1.3/contrib/lo/lo.sql
Para usar veja o README.lo no diretório lo e também a documentação oficial do PostgreSQL:
Português do Brasil - Capítulo 28:
http://pgdocptbr.sourceforge.net/pg80/largeobjects.html
Inglês - Capítulo 29: http://www.postgresql.org/docs/8.1/interactive/largeobjects.html
Nomes de Campos com espaço ou acento devem vir entre aspas duplas.
Comentários
Em SQL os comentários mais utilizados são da seguinte forma:
SELECT * FROM tabela; - - Este é um comentário
- - Este é outro comentário
Também são aceitos os comentários herdados do C:
/* Comentário herdado do C e válido em SQL */
Dicas Práticas de Uso do SQL
Testar se campo é de e-mail, ou seja, se contém um @:
SELECT POSITION('@' IN 'ribafs@gmail.com') > 0
select 'ribafs@gmail.com' ~ '@'
select 'ribafs@gmail.com' like '%@%'
select 'ribafs@gmail.com' similar to '%@%.%';
Alguns da lista de PHP (phpfortaleza@yahoogrupos.com.br - groups.yahoo.com).
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;
INSERINDO COM SELECT
Tendo uma tabela com registros e outra para onde desejo incluir registros daquela
INSERT INTO equipamentos2 SELECT grupo, insumo, descricao, unidade from equipamentos2;
insert into engenharia.precos (insumo_grupo,insumo) select grupo,insumo from engenharia;
Com CAST
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
select trim(length(bairro)) from cep_tabela where cep='60420440'; -- Montese, Retorna 7
Através do PHP
$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);
}
Diferença em Dias entre duas Datas
SELECT DATE '2006-03-29' – DATE '2006-01-12';
SELECT (CAST('10/02/2005' AS DATE) - CAST('10/01/2006'));
POPULAR BANCO COM MASSA DE TESTES
Script el Perl
- !/usr/bin/perl
$count = 1;
$arquivosaida = "populate.sql";
@chars = ("A" .. "Z", "a" .. "z", 0 .. 9);
@numbers = (1 .. 9);
@single_chars = ("a" .. "e");
$totalrecords = 5000; # 5 milhoes
open(OUTPUT, "> $arquivosaida");
print OUTPUT "DROP TABLE index_teste;\n";
print OUTPUT "CREATE TABLE index_teste (";
print OUTPUT "codigo INT, nome VARCHAR(10), numero INT, letra CHAR(1)";
print OUTPUT ");\n";
print OUTPUT "COPY index_teste (codigo, nome, numero, letra) FROM stdin;\n";
while ($count <= $totalrecords){
$randstring = join("", @chars [map{rand @chars} ( 1 .. 8 ) ]);
$randnum = join("", @numbers [map{rand @numbers} ( 1 .. 8 ) ]);
$randletter = join("", @single_chars [map{rand @single_chars} (1)]);
print OUTPUT
#print OUTPUT "INSERT INTO index_teste VALUES($count,'$randstring',$randnum,'$randletter');\n";
$count."\t".$randstring."\t".$randnum."\t".$randletter."\n";
$count++;
};
- print OUTPUT "\n";
- print OUTPUT "\nCREATE INDEX indexteste_codigo_index ON index_teste(codigo);\n";
- print OUTPUT "CREATE INDEX indexteste_numero_index ON index_teste(numero);\n";
- print OUTPUT "VACUUM ANALYZE index_teste;\n";
close OUTPUT;
Via PHP
$con=pg_connect("host=127.0.0.1 user=postgres password=postgres");
function datediff($data_final, $data_inicial){
global $con;
$str="SELECT DATE '$data_final' - DATE '$data_inicial'";
$recordset = pg_query($con, $str);
$diferença=pg_fetch_array($recordset);
return $diferença[0];
}
echo "Diferença: " . datediff("1969-01-08", "1968-10-16");
Ajustando o formato da Data do Sistema
SHOW DATESTYLE;
SET DATESTYLE TO ISO; YYYY-MM-DD HH:MM:SS
SET DATESTYLE TO PostgreSQL; Formato tradicional do PostgreSQL (
SET DATESTYLE TO US; MM/DD/YYYY
SET DATESTYLE TO NONEUROPEAN, GERMAN; DD.MM.YYYY
SET DATESTYLE TO EUROPEAN; DD/MM/YYYY
Obs.: De forma permanente ajustar o postgresql.conf.
Outros usos para SHOW:
SHOW server_version;
SHOW server_encoding; -- Idioma para ordenação do texto (definido pelo initdb)
SHOW lc_collate; -- Idioma para classificação de caracteres (definido pelo initdb)
SHOW all; -- Mostra todos os parâmetros
Também podemos setar o datestyle quando alteramos um banco:
ALTER DATABASE nomebanco SET DATESTYLE = SQL, DMY;
Também pode ser atribuído juntamente com o Usuário:
ALTER ROLE nomeuser SET DATESTYLE TO SQL, DMY;
Ajustando uma Faixa de Registros com LIMIT and OFFSET
SELECT isbn, title, publication FROM editions NATURAL JOIN books AS b (book_id)
ORDER BY publication DESC LIMIT 5;
SELECT isbn, title, publication FROM editions NATURAL JOIN books AS b (book_id)
ORDER BY publication DESC LIMIT 5 OFFSET 2;
Trará 5 registros, iniciando do segundo.
fsync - checa integridade dos dados gravados no banco, vindos dos logs. Vem ligado por padrão
Gargalo de SGBDs:
leitura/gravação (I/O) de discos.
Ligar/Desligar fsync no:
postgresql.conf, setar para
fsync=true – Nunca deve ficar false
REORDENAR CAMPOS DE TABELA
Se você estiver falando da ordem dos campos na tabela não existe razão para isso no modelo relacional.
Você sempre pode especificar os campos desejados, e na ordem desejada,
no SELECT.
Se necessário você pode criar uma view:
CREATE VIEW nome_view AS SELECT id,cpf,nome FROM sua_tabela;
Se ainda não estiver satisfeito pois quer suas tabelas "bonitinhas" e organizadas:
1. CREATE TABLE novo_nome AS SELECT id,cpf,nome FROM sua_tabela;
2. DROP TABLE sua_tabela;
3. ALTER TABLE novo_nome RENAME TO sua_tabela;
Osvaldo (Na lista PostgreSQL-Brasil).
Calculando a Memória a ser usada pelo PostgreSQL
- Shared Buffers
Exemplo de 1GB RAM
A shared buffers será 25% da RAM
256 * 1024 / 8 = 32768
logo shared_buffers = 32768
- Shared Memory
A Shared Memory será igual a shared buffer + (de 10 a 20)%
Shared Memory = 256MB + 15%
256MB + 15% = 295 MB
295MB = 295 * 1024 * 1024 = 309329920
No Linux:
/etc/sysctl.conf
kernel.shmmax = 309329920
kernel.shmall = 309329920
kernel.shmmni = 1
Comando para alterar as variáveis do kernel sem re-iniciar o Linux:
sysctl -w kernel.shmmax=309329920
sysctl -w kernel.shmall=309329920
sysctl -w kernel.shmmni=1
Dicas de instalação do PostgreSQL em GNU/Linux.
- Utilizar HD do tipo SATA
- Criar uma partição exclusiva para os dados. Ex: /database
- Utilizar nesta partição o sistema de arquivos XFS
- Deixar nesta partição apenas os flags: RW,NOATIME
Do site: http://www.gescla.com.br/oficina_postgre.asp
Criação de Tipos de Dados
CREATE TYPE "img" (input = "int4in", output = "int4out", internallength = 4, externallength = 10, delimiter = ",", send = "int4out", receive = "int4in", passedbyvalue, alignment = int, storage = plain);
Uso:
create table imagens (codigo int8, descricao varchar(60), imagem img);
Construtor de Matriz
Matriz unidimensional - array[2,4,6+2]
SELECT array[2,4,6+2]; -- Retorna 2,4,8
Multidimensional - composta por duas ou mais matrizes unidimensionais:
Obs.: O índice do valor da matriz construído com ARRAY sempre começa com um.
Ao criar uma tabela podemos usar matriz em seus tipos de dados, ao invés de tipos simples.
Exemplo:
CREATE TABLE testematriz (codigo INT [], nome char[30][30]);
array[array[2,4,6],array[1,3,5]] ou
array[[2,4,6],[1,3,5]]
Com subconsultas. Entre parênteses e não concletes.
select array(select oid from pg_proc where proname like 'bytea%');
Retorna: 1244,31,1948,1949,1950,1951,1952,1953,1954,2005,2006,2011,2412,2413,16823
ENCONTRAR REGISTROS DUPLICADOS
SELECT DISTINCT cep FROM cep_tabela
WHERE cep IN (SELECT cep FROM cep_tabela AS Tmp GROUP BY cep,tipo,logradouro, bairro, municipio,uf HAVING Count(*) >1 ) ORDER BY cep;
(Adaptação de consulta gerada pelo assistente Encontrar duplicadas do Access).
Ou:
select count(*) as quantos, cep from cep_tabela group by cep having count(*) > 1;
REMOVER DUPLICADOS
Para tabelas criadas WITH OIDS:
DELETE FROM cep_tabela2 WHERE oid NOT IN
(SELECT min(oid) FROM cep_tabela2 GROUP BY cep, tipo, logradouro, bairro, municipio, uf);
Do exemplo 8.10 do manual em português do Brasil.
Ou:
Criando uma segunda tabela que conterá somente os registros exclusivos e ainda guarda uma cópia da tabela original:
CREATE TABLE cep_tabela2 AS SELECT cep, tipo, logradouro, bairro, municipio, uf FROM cep_tabela GROUP BY cep, tipo, logradouro, bairro, municipio, uf ORDER BY cep;
Caso não importe qual das duplicatas irá permanecer:
CREATE TABLE tab_temp AS SELECT DISTINCT * FROM tabela;
DROP tabela;
ALTER TABLE tab_temp RENAME TO tabela;
(Dica de Osvaldo Rosario Kussama na lista de PostgreSQL Brasil)
Delimitadores
A maioria dos tipos de dados tem seus valores delimitados por apóstrofos (‘), a exemplo de:
caracteres
data/hora
monetário
boleanos
binários
geométricos
arrays
A exceção é para os demais tipos numéricos: date ‘18/12/2005’ numeric 12345.45
Caracteres Especiais
Para poder escrever uma barra no valor de uma constante, usa-se duas barras:
SELECT '\\Barra';
Para escrever um apóstrofo usa-se dois apóstrofos:
SELECT 'Editora OReyle';
PostgreSQL também permite o uso de caracteres de escape para escrever caracteres especiais:
SELECT 'Editora O\'Reyle';
Concatenação de expressões no terminal:
SELECT 'Concate'
'nação';
Equivale a:
SELECT 'Concatenação';
Quando resolvendo expressões matemáticas usar parênteses para tornar mais claras as precedências.
Convertendo para Números
SELECT TO_NUMBER('0' || '1,500.64',99999999.99);
Total de 8 dígitos com 2 decimais.
Variáveis no psql
\pset null '(nulo)' -- traduzindo null por nulo
SELECT NULL;
\set variavel 14 -- Dando valor 14 à variável
SELECT :variavel;
COPIAR TABELA COM REGISTROS
CREATE TABLE tabeladestino AS SELECT * FROM tabelaorigem;
Após o que teremos que recriar as constraints.
phpPgGIS
http://www.geolivre.org.br/modules/news/
A OpenGEO coloca à disposição da comunidade uma ferramenta de gerência de dados geográficos no PostgreSQL. O phpPgGIS é mais um produto da OpenGEO que contempla uma demanda na área de Geotecnologias e visa atender usuários do mundo inteiro.
Desenvolvido com base no phpPgAdmin, o phpPgGIS utiliza o MapServer para visualizar o conteúdo espacial dos campos do PostGIS com muita simplicidade (um clique). Seqüências de códigos complexos (campo de geometria) agora podem ser vistos num mapa.
Algumas Definições
Cursor
É um ponteiro para uma linha (registro).
Replicação
É a distribuição de dados corporativos para vários locais ou filiais de uma empresa, oferecendo confiabilidade, tolerância a falhas, melhor desempenho e capacidade de gerenciamento.
Criptografia
Seu objetivo é tornar os dados comuns em bits de aparência completamente aleatória.
MAIÚSCULAS E MINÚSCULAS NO POSTGRESQL
Ao digitar nomes de tabelas e campos em Maiúsculas eles serão convertidos automaticamente para minúsculas, a não ser que sejam digitados entre aspas duplas:
SELECT * FROM "CLIENTES";
Recomendação: evitar o uso de maiúsculas e de acentos em nomes de bancos, tabelas e campos.
POSTGRESQL NÃO CONECTA?
Do site do Rodrigo Hjort (http://agajorte.blogspot.com/2009/03/meu-postgresql-nao-conecta.html)
- Pingar no IP
- Verificar o pg_hba.conf - host, banco, usuário IP e senha
- Caso apareça "Is the server running on host.."
- Testar com telnet IP porta (Ctrl+C para sair)
- No postgresql.conf - listen_addresses = 'IP'
- Salvar e restartar o SGBD.
Contador de Resultados
Indicado para consultas e relatórios (não grava)
CREATE TEMP SEQUENCE seq;
SELECT nexval('seq'), * FROM esquema.tabela;
(Salvador S. Scardua na lista PostgreSQL Brasil)
LIMITES DO POSTGRESQL
Tamanho de um Banco de Dados - ilimitado
Tamanho de uma tabela - 32 TB
Quantidade de registros por tabela - ilimitados
Quantidade de campos por tabela - 250 a 1600 (depende do tipo)
Quantidade de índices por tabela - ilimitados