Usando arrays no PostgreSQL: um guia

Quando você pensa em imagens SQL de tabelas, consultas SELECT e o conceito de uma linha por ponto de dados sem estrutura aninhada provavelmente vêm à mente.

Mas e se você quisesse armazenar uma lista de números, produtos e muito mais em uma linha?

Você pode expandir a lista em muitas linhas, mas isso pode levar a uma selva de linhas que pode ser difícil de controlar. Felizmente, o PostgreSQL oferece o tipo de dados array que pode ser usado para armazenar e manipular listas.

Com ARRAY, o PostgreSQL permite que colunas de uma tabela sejam definidas como arrays multidimensionais de comprimento variável. Matrizes de qualquer tipo base, tipo enum, tipo composto, tipo de intervalo ou domínio integrado ou definido pelo usuário podem ser criadas.

Os arrays podem assumir vários formatos e há diversas maneiras de declará-los.

Você pode usar a palavra-chave ARRAY após a declaração do tipo de dados de uma coluna para indicar que deseja criar um Array do tipo de dados declarado anteriormente, conforme mostrado abaixo.

Você também pode seguir a declaração do tipo de dados com colchetes, como text[], mas acho isso menos explícito. E não está em conformidade com o padrão SQL, o que a palavra-chave version faz.

No exemplo a seguir, crio uma pequena tabela para simular um cartão de compras e preenchê-lo com algumas permutações de vários produtos. Se você executar a consulta completa, também poderá ver como as matrizes são representadas como saída.

Outra maneira de adicionar uma matriz a uma tabela é usar a sintaxe {} em vez da palavra-chave ARRAY entre colchetes. Ambas as formas funcionam. Basta prestar muita atenção às citações entre colchetes. Novamente, acho a sintaxe da palavra-chave mais explícita e os colchetes mais próximos das representações de array em outras linguagens de programação, mas ambos funcionam bem.

Mais sobre ciência de dados10 conceitos avançados de SQL que você deve conhecer para entrevistas de ciência de dados

E se você realmente precisasse explodir ou desaninhar a matriz de linhas específicas para unir alguma tabela de informações do produto ou executar outras operações com base em itens de lista?

Sem problemas. Arrays no PostgreSQL podem ser facilmente desaninhados com a palavra-chave UNNEST:

Como esperado, o UNNEST cria uma linha por item no respectivo array da linha original:

Você pode estar se perguntando se precisa desaninhar um array toda vez que quiser acessar um único item para operações adicionais. Se for esse o caso, uma estrutura de array pode não ser uma boa ideia, afinal. Felizmente, os arrays no PostgreSQL permitem o acesso por meio de indexação com colchetes e fatias, conforme mostrado abaixo. Isto permite uma seleção fácil, por exemplo, do primeiro item de cada lista ou de um intervalo específico de itens de uma lista.

A indexação em arrays PostgreSQL começa em um, e não em zero, o que pode ser diferente do que você está acostumado em outras linguagens de programação.

As fatias funcionam com a sintaxe [start:end]. Acesso ao item da matriz por meio do fatiamento para todas as linhas com mais de dois elementos da matriz.

Se você não tem certeza de quantos itens seu array possui, ou se deseja filtrar pelo tamanho de um array, como fiz acima, você pode usar a palavra-chave CARDINALITY. Isso retornará o número de itens na matriz como um número inteiro.

E se você quisesse selecionar todos os carrinhos de compras que possuem um produto específico na matriz? Ou qualquer outra operação de filtro baseada em um item da matriz? Não tem problema, o PostgreSQL permite que você use o valor do item na cláusula WHERE em conjunto com a palavra-chave ANY.

Abaixo, estou filtrando as linhas que contêm 'product_c' na coluna da matriz de produtos. Essa forma de filtragem é mais útil para valores de filtro únicos.

Você também pode estender a lógica do filtro acima e solicitar qualquer cartão de compras que contenha uma submatriz específica. Aqui estou usando o operador '@>', que significa 'contém'. Pode ser lido da seguinte forma:

“A matriz de produtos contém a matriz ['produto_a', 'produto_b'].”

Matrizes e valores de matriz podem ser atualizados de forma semelhante a outros tipos de dados, com uma cláusula UPDATE… SET…, conforme mostrado abaixo. Você pode atualizar um único item da matriz por meio da indexação ou a matriz inteira.