PostgreSQL Prático/Apêndices/Dicas Práticas de uso do SQL

15.4 - Dicas Práticas de uso do SQL

Armazenar 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

1. !/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++;

};

1. print OUTPUT "\n";

1. print OUTPUT "\nCREATE INDEX indexteste_codigo_index ON index_teste(codigo);\n";

1. print OUTPUT "CREATE INDEX indexteste_numero_index ON index_teste(numero);\n";

1. 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