tiagopassos.com

Foi liberada hoje uma atualização de segurança do PosgreSQL para todas as versões ativas (8.4, 9.0, 9.1 e 9.2). A correção feita trata especificamente um bug de alto risco para a integridade do sistema de arquivos. É extremamente recomendado que as atualizações sejam realizadas o quanto antes.

O bug é o seguinte: se alguém abrir uma conexão com o seu servidor, e utilizar um nome de banco de dados que começa com "-", o sistema de arquivos pode ser danificado. O caso é mais grave para servidores que estão expostos à internet sem a devida proteção de Firewall, o que reforça a importância de se fechar o acesso ao servidor e deixar apenas as portas necessárias, para os IP's necessários.

Segundo o site Database Soup, existem 120 mil servidores no mundo expostas à essa falha e em situação de risco.

Fonte: http://www.postgresql.org/about/news/1456/

Essa é uma dica bem especifica, mas que provavelmente vai ser útil em algum momento na vida de um programador e/ou DBA.

Como criar uma lista sequencial a partir de dados que não existem em uma tabela? Por exemplo: eu quero criar um select que retorne uma lista de 1 a 100 (cada número em uma linha). Como fazer isso? Veja aqui o exemplo:

select generate_series(1,100) as sequencia

Acho que não precisa nem explicar né? O primeiro parâmetro do generate_series é o início da lista, e o segundo o é o fim da lista.

Quer outro exemplo? Imagine que seu gerente pediu pra totalizar as vendas diárias do mes de abril de 2012, ou seja, trazer uma lista com quanto vendeu em cada dia. Mas tem um problema: no dia 3 não vendeu nada. Como você vai exibir um total de um dado que não existe? O generate_series vai te ajudar! Veja o exemplo:

select generate_series(0,29) + date '2012-04-01' as data

O select acima vai trazer uma lista com todos os dias de Abril de 2012. E eu não precisei de tabela nenhuma pra isso.

Saiba mais aqui: http://blog.redfin.com/devblog/2008/05/fun_with_generate_series.html

Aspas simples são utilizadas na estrutura de um comando SQL, sendo assim, podemos ter problemas se precisarmos exibir as aspas simples como um valor fixo. No meu caso, tinha que exibir latitude e longitude (que estava armazenado no banco em campos inteiros) e depois colocar as aspas simples e duplas fixas, ao lado do valor.

Sem mais lenga lenga, pra fazer isso você poder utilizar a função CHR, que retorna um caracter da tabela ASCII.

select
cast(chr(39) as varchar) as aspas_simples,
cast(chr(34) as varchar) as aspas_duplas

É possível que funcione da mesma forma em outros bancos.

Sem muita conversinha, vamos direto ao assunto. Para instalar o PostgreSQL 9 e o pgAdmin III no Ubuntu 10.04, acesse o Terminal (Menu Principal / Acessórios / Terminal) e digite os seguintes comandos:

sudo add-apt-repository ppa:pitti/postgresql
sudo apt-get update
sudo apt-get upgrade
sudo apt-get install postgresql
sudo apt-get install pgadmin3

O que você acabou de fazer foi instalar o PosgreSQL a partir de um repositório PPA, ou seja, um repositório que não faz parte da distribuição oficial, que traz a versão 8.4.

Depois de instalar, você vai precisar criar a senha para o usuário padrão do posgresql. Veja como fazer isso clicando aqui.

Se você não conseguiu abrir o PgAdmin depois de instalar, veja aqui como resolver.

Esses dias tive esse problema. Instalei o PostgreSQL 9 e o pgAdmin III no meu notebook com o Ubuntu 10.04 e eu simplesmente não conseguia abrir o pgAdmin. Clicava e não acontecia nada. Pesquisando um pouco, encontrei um tópico no Launchpad que me trouxe a solução. É um bug que deveria estar resolvido, mas não foi. Se você está com esse problema, abra o Terminal (Menu Principal / Acessórios / Terminal) e digite o seguinte código:

sudo apt-get remove pgadmin3
sudo apt-get build-dep pgadmin3
sudo apt-get --compile source pgadmin3

O processo demora vários minutos, então vá tomar um café enquanto é executado. O que esses comandos estão fazendo é baixando o código fonte do pgAdmin III e compilando uma versão específica para o seu Ubuntu. Depois de compilar, agora você tem que encontrar o arquivo gerado. Digite o comando:

ls pgadmin*deb

Como resultado você vai ter algo do tipo: pgadmin3_1.10.2_1_i386.deb Então é só instalar o pacote assim:

sudo dpkg -i nome_do_arquivo_gerado.deb

É isso!

Os CPFs e CNPJs em seu sistema estão cadastrados todos sem pontos, como é bastante comum. Mas agora você precisa trazê-los do banco já com a pontuação correta. Como fazer isso?

Utilizando a função SUBSTR do PostgreSQL, podemos separar um String (Varchar, por exemplo) em várias partes e depois concatenar com a pontuação desejada. Lembre-se que o campo deve estar como String. Se estiver como inteiro, você vai ter que converter utilizando o CAST. Vamos aos exemplos:

CPF

select substr(cpf, 1, 3) || '.' ||
       substr(cpf, 4, 3) || '.' ||
       substr(cpf, 7, 3) || '-' ||
       substr(cpf, 10) as cpf
       from (select cast('93746041597' as varchar) as cpf)a

CNPJ

select  substr(cnpj, 1, 2) || '.' || SUBSTR(cnpj, 3, 3) || '.' ||
        substr(cnpj, 6, 3) || '/' || SUBSTR(cnpj, 9, 4) || '-' ||
        substr(cnpj, 13) AS cnpj
        from (select cast('99325358000151' as varchar) as cnpj)a

Fonte: http://flaviosilveira.com/2009/tratando-cpf-cnpj-primeiro-nome-e-data-pelo-sql-postgre/

Você tem uma tabela nos PostgreSQL quem tem vários códigos, sendo alguns com dois caracteres, outros com três, e por aí vai. Mas como fazer para uniformizar a quantidade de caracteres, ou seja, deixar todos os códigos com a mesma quantidade de números, adicionando zeros à esquerda? Basta utilizar a função LPAD do PostgreSQL. Vamos ao exemplo:

select 
   lpad(cast(cod_curso as varchar),4,'0') as cod_curso 
   from cursos

No exemplo acima, o código do curso que possui 3 caracteres, por exemplo: 125, virou 0125. O código do curso com 2 caracteres, por exemplo: 15, virou 0015. E por aí vai.

Você pode fazer os testes utilizando o banco de testes.

Saiba mais sobre o LPAD aqui e aqui.

Utilizar conjuntos no PostgreSQL, usando recursos como a União, Interseção e Diferença podem ser bastante úteis no dia-a-dia. O mais utilizado é a União, mas a Interseção e Diferença também são necessárias em muitos momentos. Disponibilizei também uma base de testes, que você pode utilizar para testar os exemplos que vou mostrar aqui.

Para que funcionem corretamente, é necessário que os selects utilizados tenham o mesmo número de colunas e dados do mesmo tipo.

União (UNION E UNION ALL)
O UNION serve para juntar ou unir dois selects, um abaixo do outro. As linhas repetidas são ignoradas.

Exemplo 1.

SELECT nom_prof AS nome FROM professores
UNION
SELECT nom_alu AS nome FROM alunos

No exemplo 1, o primeiro select retorna os nomes de todos os professores, e o segundo retorna o nome de todos os alunos. O UNION junta todos os nomes em uma única lista.

O UNION ALL é bastante semelhante ao UNION. A única diferença é que as linhas repetidas são exibidas.

Exemplo 2:

SELECT nom_prof AS nome FROM professores
UNION ALL
SELECT nom_alu AS nome FROM alunos

O exemplo 2 faz a mesma coisa do exemplo 1, com a diferença que as linhas repetidas são exibidas no exemplo 2.

Interseção (INTERSECT)
Exibe apenas os registros que estão nos dois selects.

Exemplo 3:

SELECT nom_prof AS nome FROM professores
INTERSECT
SELECT nom_alu AS nome FROM alunos

Diferença (EXCEPT)
São exibidas as linhas que estão no primeiro select, mas não estão no segundo select.

Exemplo 4:

SELECT nom_prof AS nome FROM professores
EXCEPT
SELECT nom_alu AS nome FROM alunos

O SQL é uma linguagem padrão para os bancos de dados relacionais, como o MySQL e SQL Server, mas como a sintaxe pode mudar um pouco de um SGBD para outro, resolvi fazer esse pequeno artigo com alguns exemplos práticos de alguns selects simples com o PostgreSQL. Disponibilizei também uma base de testes, que você pode utilizar para testar os exemplos que vou mostrar aqui.

Exemplo 1:

SELECT mat_alu AS matricula, nom_alu AS nome
FROM alunos

No exemplo acima estou trazendo a matrícula e o nome dos alunos que constam na tabela alunos.

Exemplo 2:

SELECT mat_alu AS matricula, nom_alu AS nome
FROM alunos
WHERE cod_curso = 10

No exemplo 2, utilizei a clausula WHERE para trazer apenas os alunos do curso com código 10.

Exemplo 3:

SELECT mat_alu AS matricula, nom_alu AS nome
FROM alunos
WHERE cod_curso = 10 or cod_curso = 8

No exemplo 3, trago apenas os alunos de cursos dos cursos de código 8 ou 10

Exemplo 4:

SELECT mat_alu AS matricula, nom_alu AS nome
FROM alunos
WHERE nom_alu like 'A%'

No exemplo 4, trago todos os alunos que tem o nome começado pela letra 'A'

Algum tempo atrás postei um artigo com um banco de dados de exemplo para MySQL. Agora resolvi postar um para PostgreSQL. Tem uma estrutura bem diferente: é um controle acadêmico (simplificado, é claro). É bastante útil se você estiver estudando o PostgreSQL ou ministrando um treinamento básico.

Para fazer o download da base de dados, clique aqui.