PostgreSQL string_agg: order, DISTINCT, and NULL values

PostgreSQL string_agg combines values from several rows into one string, with a delimiter between them. It is useful for report fields such as a project's member names or an order's item labels. For predictable output, put the ordering inside the aggregate: string_agg(name, ', ' ORDER BY position, id).

This guide uses a small, reproducible fixture to cover grouping, duplicate removal, NULL values, FILTER, and numeric sorting. All SQL examples were run on PostgreSQL 18.6. The tests check exact results and expected errors; they are not a performance benchmark.

Create a small reporting fixture

Run the setup in a disposable database where you can create a schema. It creates four projects and seven member rows. Alpha contains a repeated name, Gamma has a member with a NULL name, and Delta has no members. Those cases make differences in the queries visible.

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

Outputs below use psql's unaligned format: | separates columns, headers are omitted, and <NULL> represents SQL NULL. The examples use psql -X -qAt -P "null=<NULL>". The marker is a display setting, not text returned by string_agg.

Use GROUP BY and order inside string_agg

The first query builds one list per project represented in the member table. The two ORDER BY clauses have separate jobs: the inner one orders the names within a string, while the final one orders the report rows.

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 produces Ana, Mira, Mira. Repeated values remain unless you ask for deduplication. Its NULL name contributes neither a value nor an extra separator. Gamma still has a group, but its aggregate is NULL because none of its names is non-NULL. Delta is absent because this query reads only the member table.

The PostgreSQL aggregate reference documents these rules. Do not rely on insertion order or the order you happened to see in an earlier run. Use an explicit sort inside the call. A secondary key, such as the unique member ID here, resolves ties in the primary key.

Remove repeated values with DISTINCT

If the report needs each distinct name once, use a constant delimiter and order by the same name expression. This example uses COLLATE "C" explicitly so its simple text ordering is reproducible.

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 now contains one occurrence of Mira. This deduplicates the text values, not people's identities. Two different people can share a name; use a stable person identifier earlier in the query if that distinction matters. Choose an appropriate collation for user-facing language rather than copying the lab's C collation without considering your data.

Fix the DISTINCT and ORDER BY restriction

An expression such as string_agg(DISTINCT name, ', ' ORDER BY position) fails in this fixture with SQLSTATE 42P10. The same name can have more than one position, so the query has not defined which position should order the retained name. PostgreSQL's aggregate expression rules restrict ORDER BY expressions when DISTINCT is used.

Define the representative row or value first. Here the rule is to keep the earliest position for each non-NULL name, then concatenate the results. Names break ties between equal first positions.

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

Mira's first position is 20, and Ana's is 10. The query therefore produces Ana, Mira for Alpha. Gamma disappears from this intermediate result because its only name is NULL and the CTE excludes NULL names. If the report must retain every project, join the aggregated result back to the project table.

The CTE makes the deduplication rule readable before the final aggregation. Keeping these steps separate also makes it easier to inspect which names and positions enter the final list.

Keep NULL, an empty string, and an empty input separate

The following queries test three cases: a NULL name between two names, an empty string before a name, and no input rows at all.

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)

The first result shows that a NULL input value is skipped. A NULL delimiter adds no separator, producing AnaLeo. An empty delimiter also joins values directly. The delimiter argument is still required; calling string_agg(name) fails.

The second result is '|Ana'. The quotes come from quote_nullable, used here to make the string's boundary visible. The empty string is retained as the first value, so the delimiter before Ana remains. If blank names should be treated as missing, express that rule before aggregation.

The final query returns one aggregate row with NULL and the display fallback (none). With GROUP BY over an empty input, there would be no groups and therefore no result rows. Placing COALESCE outside string_agg supplies a fallback for a NULL aggregate. Placing it around each input name instead inserts replacement text into the list for missing names.

Use FILTER while retaining every project

This report includes active names and all names side by side. A LEFT JOIN preserves the project rows, and FILTER changes which rows feed only the active-names aggregate.

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>

Alpha's inactive Mira row appears in the all-names list but is excluded from the active list. Gamma and Delta both display (none) for active names, although their underlying situations differ: one has a NULL-named member and the other has no members.

Moving m.active into a query-level WHERE clause would affect the whole report. In this fixture it removes Delta, and it would also stop the all-names column from seeing inactive rows. Keep the condition on the aggregate when the report needs both populations.

Cast numbers to text without losing numeric order

The text form of string_agg does not accept an integer value directly. Cast the value to text, but keep a numeric sort key when the intended order is numeric.

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

The first list is 1, 2, 10; the second is 1, 10, 2. Both contain the same values. The difference comes from sorting numbers versus sorting their text representations. Decide what the list means before choosing its sort expression.

Choose a string only when the consumer needs a string

A comma-separated display field is not automatically a CSV record. In the tests, combining Ana, Jr. and Leo produces Ana, Jr., Leo with no quoting or escaping. Use COPY with CSV format or a CSV library for an actual export.

If an application needs to keep individual values, consider an array or JSON representation instead of creating a string that must be split again. Define NULL handling and ordering for the chosen representation as well.

Also check the rows entering the aggregate after joins. An unintended one-to-many join can multiply names. DISTINCT may hide repeated text while leaving the join error unresolved. Verify counts and keys before treating deduplication as the fix.

For a slow report, inspect the actual query plan before changing memory settings or adding indexes. The PostgreSQL EXPLAIN guide shows how to read plan evidence. The small fixture here establishes result behavior, not the performance of your production query.

Clean up the example

After finishing, remove only the disposable schema created at the start. CASCADE removes its two tables and their dependent objects.

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

t

For a dependable string_agg report, decide which rows belong in each list, what makes two entries duplicates, and how ties are ordered. Then test NULL values and groups with no matching children. Those choices determine the report's meaning as much as its delimiter does.