Uso de matrices en PostgreSQL: una guía

Cuando piensas en imágenes SQL de tablas, probablemente te vienen a la mente las consultas SELECT y el concepto de una fila por punto de datos sin una estructura anidada.

Pero, ¿qué pasaría si quisieras almacenar una lista de números, productos y más dentro de una fila?

Podrías expandir la lista a muchas filas, pero esto puede generar una jungla de filas de las que puede ser difícil realizar un seguimiento. Afortunadamente, PostgreSQL ofrece el tipo de datos de matriz que se puede utilizar para almacenar y manipular listas.

Con ARRAY, PostgreSQL permite definir las columnas de una tabla como matrices multidimensionales de longitud variable. Se pueden crear matrices de cualquier tipo base, tipo de enumeración, tipo compuesto, tipo de rango o dominio integrado o definido por el usuario.

Las matrices pueden adoptar muchas formas y existen varias formas de declararlas.

Puede utilizar la palabra clave ARRAY después de la declaración del tipo de datos de una columna para indicar que desea crear una matriz del tipo de datos declarado anteriormente, como se muestra a continuación.

También puede seguir la declaración del tipo de datos con corchetes, como texto[], pero creo que esto es menos explícito. Y no se ajusta al estándar SQL, como sí lo hace la versión de la palabra clave.

En el siguiente ejemplo, creo una pequeña tabla para simular una tarjeta de compras y la lleno con algunas permutaciones de múltiples productos. Si ejecuta la consulta completa, también puede ver cómo se representan las matrices como salida.

Otra forma de agregar una matriz a una tabla es usar la sintaxis {} en lugar de la palabra clave ARRAY entre corchetes. Ambas formas funcionan. Sólo hay que prestar mucha atención a las citas entre llaves. Nuevamente, encuentro que la sintaxis de las palabras clave es más explícita y los corchetes se acercan más a las representaciones de matrices en otros lenguajes de programación, pero ambos funcionan bien.

Más sobre ciencia de datos 10 conceptos avanzados de SQL que debe conocer para entrevistas de ciencia de datos

¿Qué pasaría si realmente necesitara explotar o desagregar la matriz de filas específicas para unir alguna tabla de información del producto o realizar otras operaciones en función de los elementos de la lista?

Ningún problema. Las matrices en PostgreSQL se pueden desanidar fácilmente con la palabra clave UNNEST:

Como se esperaba, UNNEST crea una fila por elemento en la matriz respectiva en la fila original:

Quizás se pregunte si es necesario desanidar una matriz cada vez que desee acceder a un solo elemento para realizar más operaciones. Si ese es el caso, una estructura de matriz podría no ser una buena idea después de todo. Afortunadamente, las matrices en PostgreSQL permiten el acceso mediante indexación con corchetes y sectores, como se muestra a continuación. Esto permite seleccionar fácilmente, por ejemplo, el primer elemento de cada lista o un rango específico de elementos de una lista.

La indexación en matrices PostgreSQL comienza en uno, no en cero, lo que puede diferir de lo que está acostumbrado en otros lenguajes de programación.

Los sectores funcionan con la sintaxis [inicio:fin]. Acceso a elementos de matriz mediante corte para todas las filas con más de dos elementos de matriz.

Si no está seguro de cuántos elementos tiene su matriz, o si desea filtrar por el tamaño de una matriz, como lo hice anteriormente, puede usar la palabra clave CARDINALITY. Esto devolverá el número de elementos de la matriz como un número entero.

¿Qué pasaría si quisiera seleccionar todos los carritos de compras que tienen un producto específico en la matriz? ¿O cualquier otra operación de filtro basada en un elemento de matriz? No hay problema, PostgreSQL le permite usar el valor del elemento en la cláusula WHERE junto con la palabra clave ANY.

A continuación, estoy filtrando filas que contienen 'product_c' en la columna de matriz de productos. Esta forma de filtrar es más útil para valores de filtro únicos.

También puede ampliar la lógica del filtro desde arriba y solicitar cualquier tarjeta de compras que contenga un subconjunto específico. Aquí estoy usando el operador '@>', que significa 'contiene'. Se puede leer de la siguiente manera:

"La matriz de productos contiene la matriz ['producto_a', 'producto_b']".

Las matrices y los valores de las matrices se pueden actualizar de manera similar a otros tipos de datos, con una cláusula UPDATE... SET..., como se muestra a continuación. Puede actualizar un solo elemento de la matriz mediante la indexación o la matriz completa.

Si desea agregar o anteponer (insertar antes de los valores actuales) una matriz, puede usar las funciones ARRAY_APPEND y ARRAY_PREPEND en consecuencia. Asegúrese de notar la diferencia en el orden de los argumentos, que es diferente para cada función pero sigue un orden intuitivo.

Si desea deshacerse de un elemento específico de la matriz, puede usar ACTUALIZAR… CONFIGURAR… junto con la función ARRAY_REMOVE. Esto le permite eliminar un elemento de las matrices para todas las filas o solo un elemento específico cuando se usa con una cláusula WHERE.

Más sobre ciencia de datos 10 preguntas comunes de la entrevista SQL

Por último, puede concatenar matrices de PostgreSQL en una matriz más grande con ARRAY_CAT, de la siguiente manera:

¿Pueden los arreglos PostgreSQL hacer aún más? Por supuesto, dirígete a los documentos de PostgreSQL para encontrar muchas posibilidades adicionales.

Ahora tiene todas las herramientas para usar listas y estructuras anidadas dentro de sus tablas SQL. Si bien estas potentes herramientas le permiten romper con el paradigma de una fila, un registro, sólo debe utilizarlas si proporcionan un valor real y no pueden sustituirse por un enfoque más tradicional. Existen buenas razones para las mejores prácticas de SQL existentes, por lo que sus razones para evitarlas deberían ser aún mejores.