Menu

Arrays no PostgreSQL: ANY, contém (@>) e unnest

Colunas do tipo array no PostgreSQL: como declarar, índices a partir de 1, como verificar se um array contém um valor com ANY, @> e &&, unnest e as funções de array que você precisa.

Esta página tem editores executáveis. Edite, execute e veja a saída na hora.

Qualquer tipo do PostgreSQL pode ser um array. Declare a coluna como text[] ou int[], escreva os valores como '{a,b}' ou ARRAY['a', 'b'] e leia elementos com tags[1] (os índices começam em 1). Para verificar se um array contém um valor, use 'value' = ANY(tags) ou tags @> ARRAY['value'].

Os posts 1 e 2 têm a tag sql. tags[1] é o primeiro elemento e cardinality conta quantos há. As duas formas de literal guardam o mesmo valor; ARRAY[...] é mais prático quando os elementos vêm de expressões ou contêm vírgulas.

Verificar se um array contém um valor

Quatro operadores cobrem a pertinência:

TesteSignificadoVerdadeiro para '{sql,json}'
'sql' = ANY(tags)um valor está no arraysim
tags @> '{sql,json}'o array contém todos estessim
tags <@ '{sql,json,go}'todo elemento está nesta listasim
tags && '{go,json}'os arrays têm pelo menos um elemento em comumsim

Só o post 1 tem as duas tags. Os posts 2 e 3 têm um elemento em comum com {go,json}. O post 3 é o único em que no_go é falso. ALL é o par do ANY: a comparação precisa valer para todos os elementos.

O = ANY segue as regras de NULL do SQL. Se o valor não for encontrado e o array tiver um NULL, o resultado é NULL, não falso. Por isso WHERE NOT ('x' = ANY(tags)) pode descartar linhas que você esperava. @> e && retornam verdadeiro ou falso.

Um índice GIN deixa @>, <@, && e = rápidos em tabelas grandes. Ele não ajuda = ANY(column), então para buscas com índice escreva tags @> ARRAY['sql']:

CREATE INDEX posts_tags_idx ON posts USING gin (tags);

Transformar um array em linhas com unnest

O unnest retorna uma linha por elemento, então você pode contar, fazer joins ou filtrar por elementos individuais:

sql aparece em dois posts e as outras tags em um cada. Adicione WITH ORDINALITY (unnest(tags) WITH ORDINALITY AS t(tag, pos)) para obter também a posição de cada elemento. Para juntar linhas de volta num array, use array_agg (veja string_agg e array_agg).

Adicionar, remover e alterar elementos

Isso retorna {1,2,3}, {1,2,3,4}, {2,3}, {1,9,3}, 2, {20,30} e a, b, c. Num UPDATE, as mesmas funções alteram um array gravado: SET tags = array_append(tags, 'new') ou SET tags = array_remove(tags, 'old'). array_remove apaga todas as cópias do valor. string_to_array('a,b,c', ',') divide um texto num array.

Obter o tamanho de um array

cardinality(tags) retorna o número de elementos, e 0 para um array vazio. array_length(tags, 1) retorna o tamanho da dimensão 1, e NULL para um array vazio. Por isso WHERE array_length(tags, 1) = 0 nunca bate. Para testar se um array está vazio, use cardinality(tags) = 0 ou tags = '{}'.

Arrays do PostgreSQL podem ter mais de uma dimensão (int[][]), mas todos os subarrays precisam ter o mesmo tamanho, e o tamanho declarado (int[3]) não é imposto. Para dados aninhados ou irregulares, JSONB costuma ser uma escolha melhor.

Perguntas frequentes

Os arrays do PostgreSQL começam em 0 ou em 1?

Em 1 por padrão: tags[1] é o primeiro elemento. Um índice além do final retorna NULL em vez de gerar erro.

Qual a diferença entre IN e = ANY no PostgreSQL?

x IN (1, 2, 3) recebe uma lista escrita na consulta; x = ANY(array) recebe um único valor do tipo array. O ANY combina melhor com código de aplicação, porque você passa um único parâmetro array (WHERE id = ANY($1)) em vez de montar uma lista de placeholders.

Devo usar uma coluna array ou uma tabela separada?

Um array serve para uma lista curta que você lê e grava junto com a linha, como tags. Quando os itens precisam de colunas próprias, chaves estrangeiras ou são atualizados um a um, uma tabela separada com uma linha por item é mais fácil de consultar e de restringir.

Como verificar se um array está vazio no PostgreSQL?

Use cardinality(arr) = 0 ou arr = '{}'. array_length(arr, 1) = 0 nunca bate, porque array_length retorna NULL para um array vazio.

Ilustração das linguagens de programação do Coddy

Aprenda a programar com o Coddy

Começar