Comment Concaténer des Chaînes dans une Requête GROUP BY PostgreSQL

Lorsque vous travaillez avec des bases de données, en particulier avec PostgreSQL, vous pourriez rencontrer un scénario courant où vous devez concaténer des chaînes au sein d’un groupe d’enregistrements. Cela entre généralement en jeu lorsque vous avez un ensemble de données qui inclut plusieurs entrées pour une seule catégorie (comme des employés dans la même entreprise) et que vous souhaitez rassembler ces entrées en une seule chaîne pour présentation ou analyse.

Cet article de blog expliquera comment réaliser la concaténation de chaînes avec une requête GROUP BY dans PostgreSQL. Nous aborderons des solutions modernes ainsi que des approches pour les anciennes versions de PostgreSQL pour une compréhension complète.

Le Problème

Par exemple, considérons la table des employés suivante :

Vous souhaitez regrouper ces entrées par COMPANY_ID de sorte que la sortie présente une liste d’employés associée à chaque entreprise. La sortie souhaitée ressemblerait à ceci :

La Solution

PostgreSQL 9.0 ou Plus

Si vous utilisez PostgreSQL version 9.0 ou plus récente, vous pouvez utiliser la puissante fonction intégrée string_agg() pour concaténer des chaînes efficacement dans votre clause GROUP BY.

Voici comment vous pouvez écrire la requête :

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

Avec Ordre

De plus, puisque la version 9.0 de PostgreSQL prend en charge ORDER BY dans les fonctions d’agrégation, vous pouvez spécifier l’ordre des employés :

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

Cela garantit que les noms des employés sont concaténés dans une séquence spécifique.

PostgreSQL 8.4.x

Si vous travaillez encore avec PostgreSQL 8.4, qui est une version qui ne reçoit plus de support, vous pouvez utiliser la fonction array_agg() en combinaison avec array_to_string() pour obtenir des résultats similaires. Voici la requête SQL que vous utiliseriez :

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

PostgreSQL 8.3.x et Anciennes Versions

Pour les utilisateurs de PostgreSQL 8.3 et des versions antérieures, il n’existe pas de fonction intégrée pour réaliser directement la concaténation de chaînes. L’implémentation personnalisée suivante peut contourner cette limitation :

1. Créez une nouvelle fonction d’agrégation en utilisant la fonction textcat :

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

2. Pour inclure des séparateurs comme , entre les chaînes concaténées, créez une fonction personnalisée :

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;

Cela concaténera les chaînes de manière appropriée.

Si vous souhaitez supprimer les virgules supplémentaires pour les valeurs NULL ou vides, voici une version plus raffinée :

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;

Conclusion

Concaténer des chaînes dans PostgreSQL, surtout lors des opérations GROUP BY, est crucial pour générer des rapports et des ensembles de données significatifs. Avec l’introduction de fonctions comme string_agg() dans les versions récentes, cette tâche est devenue simple.

Si vous êtes sur une ancienne version, utiliser les méthodes d’agrégation personnalisées décrites ici peut aboutir à des résultats similaires. Pensez toujours à mettre à jour votre version de PostgreSQL pour tirer parti des nouvelles fonctionnalités et des améliorations de sécurité.

Bonne requête !