Cualquier tipo de PostgreSQL puede ser un array. Declara la columna como text[] o int[], escribe los valores como '{a,b}' o ARRAY['a', 'b'], y lee elementos con tags[1] (los índices empiezan en 1). Para comprobar si un array contiene un valor, usa 'value' = ANY(tags) o tags @> ARRAY['value'].
Los posts 1 y 2 tienen la etiqueta sql. tags[1] es el primer elemento y cardinality los cuenta. Las dos formas de literal guardan el mismo valor; ARRAY[...] es más cómodo cuando los elementos salen de expresiones o contienen comas.
Comprobar si un array contiene un valor
Cuatro operadores cubren la pertenencia:
| Prueba | Significado | Verdadero para '{sql,json}' |
|---|---|---|
'sql' = ANY(tags) | un valor está en el array | sí |
tags @> '{sql,json}' | el array contiene todos estos | sí |
tags <@ '{sql,json,go}' | cada elemento está en esta lista | sí |
tags && '{go,json}' | los arrays comparten al menos un elemento | sí |
Solo el post 1 tiene las dos etiquetas. Los posts 2 y 3 comparten un elemento con {go,json}. El post 3 es el único donde no_go es falso. ALL es la contraparte de ANY: la comparación tiene que cumplirse para todos los elementos.
= ANY sigue las reglas de NULL de SQL. Si el valor no aparece y el array contiene un NULL, el resultado es NULL, no falso. Por eso WHERE NOT ('x' = ANY(tags)) puede descartar filas que esperabas. @> y && devuelven verdadero o falso.
Un índice GIN hace rápidos @>, <@, && y = en tablas grandes. No ayuda a = ANY(column), así que para búsquedas con índice escribe tags @> ARRAY['sql']:
CREATE INDEX posts_tags_idx ON posts USING gin (tags);
Convertir un array en filas con unnest
unnest devuelve una fila por elemento, así puedes contar, hacer joins o filtrar por elementos sueltos:
sql aparece en dos posts y las demás etiquetas en uno cada una. Añade WITH ORDINALITY (unnest(tags) WITH ORDINALITY AS t(tag, pos)) para obtener también la posición de cada elemento. Para volver a juntar filas en un array, usa array_agg (ver string_agg y array_agg).
Añadir, quitar y cambiar elementos
Esto devuelve {1,2,3}, {1,2,3,4}, {2,3}, {1,9,3}, 2, {20,30} y a, b, c. En un UPDATE las mismas funciones cambian un array guardado: SET tags = array_append(tags, 'new') o SET tags = array_remove(tags, 'old'). array_remove borra todas las copias del valor. string_to_array('a,b,c', ',') divide un texto en un array.
Obtener la longitud de un array
cardinality(tags) devuelve el número de elementos, y 0 para un array vacío. array_length(tags, 1) devuelve la longitud de la dimensión 1, y NULL para un array vacío. Por eso WHERE array_length(tags, 1) = 0 nunca coincide. Para detectar un array vacío usa cardinality(tags) = 0 o tags = '{}'.
Los arrays de PostgreSQL pueden tener más de una dimensión (int[][]), pero todos los subarrays deben tener la misma longitud, y el tamaño declarado (int[3]) no se hace cumplir. Para datos anidados o irregulares, JSONB suele encajar mejor.
Preguntas frecuentes
¿Los arrays de PostgreSQL empiezan en 0 o en 1?
En 1 por defecto: tags[1] es el primer elemento. Un índice que se pasa del final devuelve NULL en lugar de dar un error.
¿Qué diferencia hay entre IN y = ANY en PostgreSQL?
x IN (1, 2, 3) recibe una lista escrita en la consulta; x = ANY(array) recibe un único valor de tipo array. ANY encaja mejor en el código de una aplicación, porque puedes pasar un solo parámetro array (WHERE id = ANY($1)) en vez de construir una lista de marcadores.
¿Uso una columna array o una tabla aparte?
Un array sirve para una lista corta que lees y escribes junto con su fila, como las etiquetas. Cuando los elementos necesitan sus propias columnas, claves foráneas o se actualizan uno a uno, una tabla aparte con una fila por elemento es más fácil de consultar y restringir.
¿Cómo compruebo si un array está vacío en PostgreSQL?
Usa cardinality(arr) = 0 o arr = '{}'. array_length(arr, 1) = 0 nunca coincide, porque array_length devuelve NULL para un array vacío.