Tweet

Arrays em colunas no PostgreSQL

Uma das regras da normalização de relações em modelagem de dados é que devemos ter apenas valores atômicos nas colunas das tabelas, evitando campos multivalorados. Porém, suponhamos que, por algum motivo qualquer, eu precise armazenar múltiplos dados em uma mesma coluna – por design, complexidade, espaço, desejo do cliente, ou outro motivo qualquer.

Neste caso, uma solução possível seria o emprego de arrays como tipo de dado da coluna.

Um array é uma estrutura de dados homogênea que representa um conjunto de elementos de mesmo tipo. Podemos acessar esses elementos (dados) individuais por meio de uma posição de índice, geralmente numérica. No PostgreSQL essa posição se inicia em 1.

Criar coluna de array no PostgreSQL

A seguir as duas sintaxes empregadas para configurar uma coluna para uso de arrays ao criar uma tabela: a sintaxe padrão do PostgreSQL e na sequência, a sintaxe padrão ANSI SQL. Ambas funcionam no Postgres.

SINTAXE (padrão PostgreSQL):

coluna tipo[]

SINTAXE (padrão ANSI SQL):

coluna tipo ARRAY[tamanho]
ou
coluna tipo ARRAY

O PostgreSQL não impõe restrição de tamanho ao array, mesmo se for especificado. Por padrão, a contagem dos elementos do array inicia em 1, em vez de 0, como é feito na maior parte das linguagens de programação.

Vamos criar uma tabela de exemplo para estudarmos o emprego de arrays em colunas.

Criar tabela com array (sintaxe PostgreSQL)

CREATE TABLE escalaTrabalho (
codFunc smallint,
escala char(3)[]
);

Agora vamos inserir três registros para que possamos rodar algumas consultas de teste na sequência:

Inserir dados na tabela para testes (sintaxe PostgreSQL)

INSERT INTO escalaTrabalho (codFunc, escala)
VALUES
(1,'{"SEG", "TER", "QUA"}'),
(2,'{"QUI", "SEX", "SAB"}),
(3,'{"SEG", "QUA", "SEX"});

Consultas de teste

Vamos testar nossa tabela com algumas consultas aos dados do array da coluna escala.

1. Mostrar todos os dados de escala dos funcionários

SELECT * FROM escalaTrabalho;

2. Retornar a escala de trabalho do funcionário de ID 2

SELECT escala FROM escalaTrabalho
WHERE codFunc = 2;

3. Em qual dia da semana cada funcionário começa a trabalhar?

SELECT codFunc, escala[1] FROM escalaTrabalho;

4. Quais funcionários trabalham às quartas-feiras?

SELECT codFunc FROM escalaTrabalho
WHERE 'QUA' = ANY(escala);
# seguir esta ordem na cláusula where, ou não funcionará.

5. Vamos alterar escala de trabalho do funcionário 3 – ele vai passar a trabalhar às terças, quintas e sábados:

UPDATE escalaTrabalho
SET escala = ARRAY['TER','QUI','SAB']
WHERE codFunc = 3;
# usei a sintaxe padrão ANSI SQL neste exemplo.

Verificamos o resultado com uma consulta simples:

SELECT * FROM escalaTrabalho;

Para saber mais sobre o uso de arrays no PostgreSQL, consulte a documentação Oficial: https://www.postgresql.org/docs/9.0/arrays.html