Cómo Concatenar Cadenas en una Consulta Group By de PostgreSQL

Al trabajar con bases de datos, particularmente con PostgreSQL, puedes encontrar un escenario común donde necesitas concatenar cadenas dentro de un grupo de registros. Esto generalmente ocurre cuando tienes un conjunto de datos que incluye múltiples entradas para una sola categoría (como empleados en la misma empresa) y deseas recoger esas entradas en una sola cadena para presentación o análisis.

En esta entrada del blog se explicará cómo lograr la concatenación de cadenas con una consulta GROUP BY en PostgreSQL. Cubriremos soluciones modernas así como enfoques para versiones más antiguas de PostgreSQL para una comprensión integral.

El Problema

Por ejemplo, considera la siguiente tabla de empleados:

Deseas agrupar estas entradas por COMPANY_ID de tal manera que la salida presente una lista de empleados asociados con cada empresa. La salida deseada se vería así:

La Solución

PostgreSQL 9.0 o Posterior

Si estás utilizando una versión de PostgreSQL 9.0 o posterior, puedes usar la poderosa función incorporada string_agg() para concatenar cadenas dentro de tu cláusula GROUP BY de manera efectiva.

Así es como puedes escribir la consulta:

SELECT company_id, string_agg(employee, ', ')
FROM mytable
GROUP BY company_id;

Con Ordenamiento

Además, dado que la versión 9.0 de PostgreSQL admite ORDER BY dentro de funciones de agregación, puedes especificar el orden de los empleados:

SELECT company_id, string_agg(employee, ', ' ORDER BY employee)
FROM mytable
GROUP BY company_id;

Esto asegura que los nombres de los empleados se concatenen en una secuencia específica.

PostgreSQL 8.4.x

Si aún estás trabajando con PostgreSQL 8.4, que es una versión que ya no recibe soporte, puedes usar la función array_agg() en combinación con array_to_string() para lograr resultados similares. Aquí tienes la consulta SQL que usarías:

SELECT company_id, array_to_string(array_agg(employee), ', ')
FROM mytable
GROUP BY company_id;

PostgreSQL 8.3.x y Anteriores

Para los usuarios de PostgreSQL 8.3 y versiones anteriores, no hay una función incorporada para realizar la concatenación de cadenas directamente. La siguiente implementación personalizada puede eludir esta limitación:

1. Crea una nueva función de agregación utilizando la función textcat:

CREATE AGGREGATE textcat_all(
  basetype    = text,
  sfunc       = textcat,
  stype       = text,
  initcond    = ''
);

2. Para incluir separadores como , entre cadenas concatenadas, crea una función personalizada:

CREATE FUNCTION commacat(acc text, instr text) RETURNS text AS $$ 
BEGIN 
  IF acc IS NULL OR acc = '' THEN 
    RETURN instr; 
  ELSE 
    RETURN acc || ', ' || instr; 
  END IF; 
END; 
$$ LANGUAGE plpgsql; 

Esto concatenará las cadenas adecuadamente.

Si deseas eliminar comas extra para valores NULL o vacíos, aquí hay una versión más refinada:

CREATE FUNCTION commacat_ignore_nulls(acc text, instr text) RETURNS text AS $$ 
BEGIN 
  IF acc IS NULL OR acc = '' THEN 
    RETURN instr; 
  ELSIF instr IS NULL OR instr = '' THEN 
    RETURN acc; 
  ELSE 
    RETURN acc || ', ' || instr; 
  END IF; 
END; 
$$ LANGUAGE plpgsql; 

Conclusión

Concatenar cadenas en PostgreSQL, especialmente durante operaciones GROUP BY, es crucial para generar informes y conjuntos de datos significativos. Con la introducción de funciones como string_agg() en versiones recientes, esta tarea se ha vuelto sencilla.

Si estás en una versión más antigua, utilizar los métodos de agregación personalizados detallados aquí puede lograr resultados similares. Siempre considera actualizar tu versión de PostgreSQL para aprovechar las nuevas características y mejoras de seguridad.

¡Feliz consulta!