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:
| Teste | Significado | Verdadeiro para '{sql,json}' |
|---|---|---|
'sql' = ANY(tags) | um valor está no array | sim |
tags @> '{sql,json}' | o array contém todos estes | sim |
tags <@ '{sql,json,go}' | todo elemento está nesta lista | sim |
tags && '{go,json}' | os arrays têm pelo menos um elemento em comum | sim |
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.