PostgreSQL string_agg : ordre, DISTINCT et valeurs NULL

Dans PostgreSQL, string_agg réunit les valeurs de plusieurs lignes dans une chaîne, avec un séparateur entre elles. La fonction convient aux champs de rapport comme les noms des membres d'un projet ou les libellés des articles d'une commande. Pour obtenir un ordre prévisible, placez le tri dans l'agrégat : string_agg(name, ', ' ORDER BY position, id).

Ce guide utilise un petit jeu de données reproductible pour tester le regroupement, la suppression des doublons, les valeurs NULL, FILTER et le tri numérique. Tous les exemples SQL ont été exécutés sur PostgreSQL 18.6. Les tests vérifient les résultats exacts et les erreurs attendues ; ils ne mesurent pas les performances.

Créer un petit jeu de données de rapport

Exécutez la préparation dans une base de test où vous pouvez créer un schéma. Elle ajoute quatre projets et sept lignes de membres. Alpha contient un nom répété, Gamma possède un membre dont le nom est NULL, et Delta n'a aucun membre. Ces cas rendent les différences entre les requêtes visibles.

CREATE SCHEMA stringagg_lab;
CREATE TABLE stringagg_lab.projects (
    id integer PRIMARY KEY,
    name text NOT NULL
);
CREATE TABLE stringagg_lab.members (
    id integer PRIMARY KEY,
    project_id integer REFERENCES stringagg_lab.projects(id),
    name text,
    position integer NOT NULL,
    active boolean NOT NULL
);
INSERT INTO stringagg_lab.projects VALUES
    (1, 'Alpha'), (2, 'Beta'), (3, 'Gamma'), (4, 'Delta');
INSERT INTO stringagg_lab.members VALUES
    (1, 1, 'Mira', 30, true),
    (2, 1, 'Ana',  10, true),
    (3, 1, 'Mira', 20, false),
    (4, 1, NULL,   40, true),
    (5, 2, 'Leo',  20, false),
    (6, 2, 'Ana',  10, true),
    (7, 3, NULL,   10, true);
SELECT count(*) FROM stringagg_lab.members;

7

Les sorties utilisent le format non aligné de psql : | sépare les colonnes, les en-têtes sont omis et <NULL> représente SQL NULL. Les exemples utilisent psql -X -qAt -P "null=<NULL>". Ce marqueur est un réglage d'affichage, pas du texte renvoyé par string_agg.

Utiliser GROUP BY et trier dans string_agg

La première requête construit une liste par projet présent dans la table des membres. Les deux clauses ORDER BY ont des rôles distincts : celle de l'agrégat ordonne les noms dans chaque chaîne, tandis que la dernière trie les lignes du rapport.

SELECT project_id,
       string_agg(name, ', ' ORDER BY position, id) AS names
FROM stringagg_lab.members
GROUP BY project_id
ORDER BY project_id;

1|Ana, Mira, Mira
2|Ana, Leo
3|<NULL>

Alpha produit Ana, Mira, Mira. Les répétitions restent présentes tant que vous ne demandez pas leur suppression. Son nom NULL n'ajoute ni valeur ni séparateur. Gamma conserve un groupe, mais son agrégat vaut NULL, car aucun nom n'est renseigné. Delta est absent, puisque cette requête lit uniquement la table des membres.

La référence des agrégats PostgreSQL (en anglais) décrit ces règles. Ne vous fiez pas à l'ordre d'insertion ni à celui observé lors d'une exécution précédente. Spécifiez le tri dans l'appel. Une clé secondaire, comme l'identifiant unique du membre ici, départage les égalités sur la première clé.

Supprimer les valeurs répétées avec DISTINCT

Pour afficher chaque nom distinct une seule fois, utilisez un séparateur constant et triez selon la même expression de nom. Cet exemple indique explicitement COLLATE "C" pour rendre son tri textuel simple reproductible.

SELECT project_id,
       string_agg(DISTINCT name COLLATE "C", ', '
                  ORDER BY name COLLATE "C") AS names
FROM stringagg_lab.members
GROUP BY project_id
ORDER BY project_id;

1|Ana, Mira
2|Ana, Leo
3|<NULL>

Alpha ne contient plus qu'une occurrence de Mira. Cette opération déduplique les textes, pas les identités. Deux personnes peuvent porter le même nom ; utilisez un identifiant stable en amont si cette distinction compte. Pour un affichage destiné aux lecteurs, choisissez une collation adaptée à la langue plutôt que de reprendre automatiquement celle du test.

Corriger la restriction entre DISTINCT et ORDER BY

Une expression comme string_agg(DISTINCT name, ', ' ORDER BY position) échoue ici avec le code SQLSTATE 42P10. Un même nom peut avoir plusieurs positions, et la requête ne précise pas laquelle doit ordonner le nom conservé. Les règles des expressions d'agrégat (en anglais) limitent les expressions ORDER BY lorsqu'on utilise DISTINCT.

Définissez d'abord la ligne ou la valeur représentative. Ici, la règle retient la plus petite position de chaque nom non NULL, puis réunit les résultats. Les noms départagent les premières positions identiques.

WITH first_occurrence AS (
    SELECT project_id, name COLLATE "C" AS name,
           min(position) AS first_position
    FROM stringagg_lab.members
    WHERE name IS NOT NULL
    GROUP BY project_id, name COLLATE "C"
)
SELECT project_id,
       string_agg(name, ', ' ORDER BY first_position, name) AS names
FROM first_occurrence
GROUP BY project_id
ORDER BY project_id;

1|Ana, Mira
2|Ana, Leo

La première position de Mira est 20, celle d'Ana est 10. La requête produit donc Ana, Mira pour Alpha. Gamma disparaît de ce résultat intermédiaire, car son seul nom est NULL et la CTE exclut les noms NULL. Pour conserver tous les projets, joignez le résultat agrégé à la table des projets.

La CTE rend la règle de déduplication lisible avant l'agrégation finale. Séparer ces étapes facilite aussi l'inspection des noms et des positions qui alimentent la liste finale.

Distinguer NULL, une chaîne vide et une entrée sans lignes

Les requêtes suivantes testent trois situations : un nom NULL entre deux noms, une chaîne vide avant un nom, puis l'absence totale de lignes en entrée.

SELECT string_agg(value, ', ' ORDER BY ord) AS names,
       string_agg(value, NULL ORDER BY ord) AS no_separator
FROM (VALUES (1, 'Ana'), (2, NULL), (3, 'Leo')) AS v(ord, value);

SELECT quote_nullable(string_agg(value, '|' ORDER BY ord)) AS shown
FROM (VALUES (1, ''), (2, 'Ana'), (3, NULL)) AS v(ord, value);

SELECT string_agg(name, ', ') AS names,
       COALESCE(string_agg(name, ', '), '(none)') AS display_names
FROM stringagg_lab.members
WHERE false;

Ana, Leo|AnaLeo
'|Ana'
<NULL>|(none)

Le premier résultat montre que la valeur NULL est ignorée. Un séparateur NULL n'ajoute aucun caractère, ce qui donne AnaLeo. Un séparateur vide réunit également les valeurs directement. L'argument du séparateur reste obligatoire : l'appel string_agg(name) échoue.

Le deuxième résultat est '|Ana'. Les guillemets proviennent de quote_nullable, utilisé pour rendre les limites de la chaîne visibles. La chaîne vide reste la première valeur, donc le séparateur qui précède Ana est conservé. Si les noms vides doivent être considérés comme absents, exprimez cette règle avant l'agrégation.

La dernière requête renvoie une ligne d'agrégat avec NULL et le texte de remplacement (none). Avec GROUP BY sur une entrée vide, il n'y aurait aucun groupe et donc aucune ligne de résultat. COALESCE placé autour de string_agg remplace un agrégat NULL. Placé autour de chaque nom, il insère au contraire du texte dans la liste pour les noms manquants.

Utiliser FILTER tout en conservant tous les projets

Ce rapport affiche côte à côte les noms actifs et tous les noms. LEFT JOIN préserve les lignes des projets, et FILTER modifie uniquement les lignes reçues par l'agrégat des noms actifs.

SELECT p.name,
       COALESCE(
           string_agg(m.name, ', ' ORDER BY m.position, m.id)
               FILTER (WHERE m.active),
           '(none)'
       ) AS active_names,
       string_agg(m.name, ', ' ORDER BY m.position, m.id) AS all_names
FROM stringagg_lab.projects AS p
LEFT JOIN stringagg_lab.members AS m ON m.project_id = p.id
GROUP BY p.id, p.name
ORDER BY p.id;

Alpha|Ana, Mira|Ana, Mira, Mira
Beta|Ana|Ana, Leo
Gamma|(none)|<NULL>
Delta|(none)|<NULL>

La ligne inactive de Mira dans Alpha apparaît dans la liste complète, mais pas dans la liste active. Gamma et Delta affichent tous deux (none) pour les noms actifs, alors que leurs données diffèrent : l'un a un membre au nom NULL, l'autre n'a aucun membre.

Déplacer m.active dans un WHERE au niveau de la requête affecterait tout le rapport. Dans ce jeu de données, cela supprime Delta et empêche aussi la colonne complète de recevoir les lignes inactives. Conservez la condition sur l'agrégat lorsque le rapport a besoin des deux populations.

Convertir les nombres en texte en conservant le tri numérique

La forme textuelle de string_agg n'accepte pas directement une valeur entière. Convertissez la valeur en texte, mais conservez une clé de tri numérique si l'ordre attendu est numérique.

SELECT string_agg(n::text, ', ' ORDER BY n) AS numeric_order,
       string_agg(n::text, ', ' ORDER BY n::text COLLATE "C") AS text_order
FROM (VALUES (2), (10), (1)) AS v(n);

1, 2, 10|1, 10, 2

La première liste est 1, 2, 10 ; la seconde est 1, 10, 2. Elles contiennent les mêmes valeurs. La différence vient du tri des nombres ou de leurs représentations textuelles. Définissez le sens de la liste avant de choisir son expression de tri.

Choisir une chaîne lorsque le destinataire attend une chaîne

Un champ d'affichage séparé par des virgules n'est pas automatiquement un enregistrement CSV. Dans les tests, réunir Ana, Jr. et Leo produit Ana, Jr., Leo, sans guillemets ni échappement. Pour un export, utilisez COPY au format CSV (documentation en anglais) ou une bibliothèque CSV.

Si une application doit conserver les valeurs individuelles, envisagez un tableau ou une représentation JSON plutôt qu'une chaîne qu'il faudra découper ensuite. Définissez également le traitement des NULL et l'ordre dans la représentation choisie.

Vérifiez aussi les lignes qui alimentent l'agrégat après les jointures. Une jointure un-à-plusieurs involontaire peut multiplier les noms. DISTINCT peut cacher les textes répétés sans corriger la jointure. Contrôlez les nombres de lignes et les clés avant de considérer la déduplication comme la solution.

Si le rapport est lent, examinez le plan réel de la requête avant de modifier la mémoire ou d'ajouter des index. Le guide PostgreSQL EXPLAIN (en anglais) explique comment lire ce plan. Le petit jeu de données présenté ici établit le comportement des résultats, pas les performances de votre requête en production.

Nettoyer l'exemple

Une fois les essais terminés, supprimez uniquement le schéma de test créé au début. CASCADE retire ses deux tables et les objets qui en dépendent.

DROP SCHEMA stringagg_lab CASCADE;
SELECT to_regnamespace('stringagg_lab') IS NULL AS removed;

t

Pour obtenir un rapport string_agg fiable, décidez quelles lignes appartiennent à chaque liste, ce qui constitue un doublon et comment départager les égalités. Testez ensuite les NULL et les groupes sans enfants correspondants. Ces choix déterminent le sens du rapport autant que son séparateur.