GROUP_CONCAT no Postgres

O MySQL tem uma função muito legal chamada GROUP_CONCAT. Ela concatena os valores de uma coluna em uma string separados por um separador que por padrão é uma vírgula(,). Para exemplificar considere a seguinte estrutura no banco de dados(pode ser usada tanto no MySQL quanto no Postgres):

create table countries_languages(
country_code varchar(4) not null,
language varchar(32) not null,
primary key(country_code, language)
);

insert into countries_languages(country_code, language) values('BR', 'Portuguese');
insert into countries_languages(country_code, language) values('THA', 'Chinese');
insert into countries_languages(country_code, language) values('THA', 'Khmer');
insert into countries_languages(country_code, language) values('THA', 'Kuy');
insert into countries_languages(country_code, language) values('THA', 'Lao');


Ao executarmos a seguinte consulta:

select country_code, language as languages
from countries_languages
where country_code = 'THA'


Obteremos a seguinte saída:

THA	Chinese
THA	Khmer
THA	Kuy
THA	Lao

Usando o GROUP_CONCAT nesta outra consulta:

select country_code, GROUP_CONCAT(language) as languages
from countries_languages
where country_code = 'THA'
GROUP BY country_code


A saída será:

THA	Chinese,Khmer,Kuy,Lao

Certo! Bem legal e útil quando quisermos exportar dados ou apresentá-los, mas como podemos fazer isso no banco de dados Postgres?

No Postgres não existe a função GROUP_CONCAT mas nele temos arrays. Uma forma de ter a mesma saída que o MySQL usando arrays em Postgres é:

select country_code, array_to_string(array(SELECT language FROM countries_languages WHERE country_code = 'THA'), ',')
from countries_languages
where country_code = 'THA'
GROUP BY country_code


O resultado da sub-consulta "SELECT language FROM countries_languages WHERE country_code = 'THA'" é convertido para um array que por sua vez é convertido para uma string pela função array_to_string com cada valor do array separado por uma vírgula. É mais verboso e exige uma sub-query mas o resultado é o mesmo.

Referência:http://stackoverflow.com/a/4469002

Truques SQL

Inserindo a partir de uma consulta SQL

Supondo que temos a seguinte estrutura de tabelas:

CREATE TABLE users(

id int not null,

name text not null,

email text

);

CREATE TABLE user_searches_results(

id int not null,

id_user int not null,

search_date date

);

Agora como nós podemos guardar todos os id's da tabela users na tabela user_searches_results retornados na seguinte consulta: SELECT id FROM users WHERE name LIKE 'Maria%' ?

Este seguinte comando sql é capaz de fazer a tarefa de forma muito eficiente:

INSERT INTO user_searches_results(id, id_user, search_date)

SELECT 1, id, now() FROM users WHERE name LIKE 'Maria%';

Simples não?

Retornando um registro aleatório a partir de uma consulta

MySQL

SELECT column1, column2, ... FROM table ORDER BY rand() LIMIT 1

PostgreSQL

SELECT column1, column2, ... FROM table ORDER BY random() LIMIT 1

SQL Server

SELECT TOP 1 column1, column2, ... FROM table

ORDER BY NEWID()

DB2

SELECT column1, column2, RAND() as IDX 

FROM table 

ORDER BY IDX FETCH FIRST 1 ROWS ONLY

Oracle

SELECT column1 FROM

( SELECT column1 FROM table

ORDER BY dbms_random.value )

WHERE rownum = 1

Selecionar o segundo maior(ou menor valor) de uma coluna

SELECT MAX(column)

  FROM table

 WHERE column < (SELECT MAX(column) FROM table )

Use MIN se você quiser o segundo menor valor e não se esqueça do operador >.

Copiar a estrutura de uma tabela sem copiar os dados(MySQL, PostgresSQL)

CREATE TABLE users_2 AS (SELECT * FROM users WHERE 1=0);

Observe que índices e constraints não são duplicados.

Gerar SQL para atualizar os dados de uma tabela baseado no conteúdo de outra tabela(PostgresSQL)

Considerem a seguinte estrutura e dados:

CREATE TABLE departments(

id int not null,

name text not null,

employee_count int

);

INSERT INTO departments values(1, 'Development', 0);

INSERT INTO departments values(2, 'Sales', 0);

INSERT INTO departments values(3, 'Marketing', 0);

CREATE TABLE employees(

id int not null,

id_department int not null,

name text not null

);

INSERT INTO employees VALUES(1, 2, 'Ryu');

INSERT INTO employees VALUES(2, 2, 'Ken');

INSERT INTO employees VALUES(3, 3, 'Chun-Li');

INSERT INTO employees VALUES(4, 1, 'Guile');

Certo. Agora precisamos atualizar a tabela departments com o número de empregados que cada uma contém. Com o seguinte sql os comandos de update serão gerados automaticamente:

SELECT 'UPDATE departments SET employee_count = ' || COUNT(e.id) 

  || ' WHERE id = ' || e.id_department || ';'

FROM employees e

GROUP BY e.id_department;

A linguagem SQL pode ser mais poderosa e útil do você imagina.

Um banco de dados de cidades e estados

Olá pessoal!
Estou trabalhando em um pequeno projeto e nele necessito ter acesso ao nome de todas as cidades do Brasil, assim como o estado de cada uma. Depois de algumas buscas na internet eu efetivamente achei alguns bancos de dados prontos contendo tabelas com os nomes das cidades e também o estado de cada uma.
Acontece que estas bases de dados que achei possuem alguns problemas. Uma que achei tinha mais de 9000 registros de cidades, sendo que o Brasil possui oficialmente um pouco mais de 5500 cidades. E também em todas as bases que achei o formato dos dados não estava do jeito que eu queria, todos os nomes dos municípios estavam com todos os caracteres em maiúsculo e também não tinham os acentos devidos.
Pesquisando um pouco mais achei no site do IBGE uma relação com todos os municípios e seus respectivos estados do ano de 2007 (para meu caso está atualizado o bastante), os arquivos estão neste endereço. O problema é que todos estes dados estão divididos em arquivos no formato xls, sendo um arquivo para cada estado. Usando a excelente biblioteca excel_reader eu fiz um pequeno script para pegar todos os dados dos arquivos xls (somente a sigla do estado e o nome de cada cidade) e transportá-los para uma tabela do banco de dados MySQL.
Junto com os scripts tem um arquivo chamado constants.php com os seguintes parâmetros de configuração:

DATABASE_HOST => Endereço do servidor mysql.
DATABASE_USER => Usuário para acessar o servidor mysql.
DATABASE_PASS => Senha do usuário mysql.
DATABASE_NAME => Nome do banco de dados onde a tabela de cidades será criada.
CITIES_TABLE_NAME => Nome da tabela com os dados das cidades que será criada.
ID_NAME_COLUMN_NAME => Nome da coluna chave primária da tabela de cidades.
ESTATE_NAME_COLUMN_NAME => Nome da coluna com o estado de cada cidade.
CITY_NAME_COLUMN_NAME => Nome da coluna com o nome de cada cidade.

Depois de configurar estas opções de acordo com seu gosto é só rodar o arquivo main.php. Os scripts podem ser baixados aqui. Junto deles vai uma pasta chamada data que contém todos os arquivos xls do IBGE.
Aqui vai um link para uma exportação da tabela de cidades que criei usando os scripts caso você queira importar os dados diretamente. A tabela possui 5518 registros.
Novamente gostaria de falar da qualidade da bilbioteca excel_reader para ler os arquivos xls. Foi uma das duas que consegui fazer rodar mas a única que não teve problemas com uso de memória e tempo de execução.
Valeu e até a próxima.