Menu

Arrays en PostgreSQL: ANY, contiene (@>) y unnest

Columnas de tipo array en PostgreSQL: cómo declararlas, índices desde 1, comprobar si un array contiene un valor con ANY, @> y &&, unnest y las funciones de arrays que necesitas.

Esta página incluye editores ejecutables: edita, ejecuta y ve el resultado al instante.

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:

PruebaSignificadoVerdadero para '{sql,json}'
'sql' = ANY(tags)un valor está en el arraysí
tags @> '{sql,json}'el array contiene todos estossí
tags <@ '{sql,json,go}'cada elemento está en esta listasí
tags && '{go,json}'los arrays comparten al menos un elementosí

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.

Ilustración de los lenguajes de programación de Coddy

Aprende a programar con Coddy

Comenzar