How to concatenate strings of a string field in a PostgreSQL 'group by' query?

Custom sequence and null handling

Need your concatenated strings in a specific order? STRING_AGG to the rescue with the ORDER BY clause:

SELECT group_column, STRING_AGG(string_column, ', ' ORDER BY order_column) AS ordered_combined_strings
FROM your_table
GROUP BY group_column;


For ancient PostgreSQL versions (pre 9.0), STRING_AGG isn’t available. Fear not, the dynamic duo of array_agg and array_to_string gets the job done:

SELECT group_column, array_to_string(array_agg(string_column), ', ') AS combined_strings
FROM your_table
GROUP BY group_column;


Stumped by NULL values? Consider a custom aggregate function, or a conditional concatenation approach for a clean, comma-separated list.

Crafting custom aggregates for tricky scenarios

Null-handling concatenation

Exclude nulls and trailing commas from your concatenation with a custom function - enter commacat:

CREATE OR REPLACE FUNCTION commacat(acc text, instr text) RETURNS text AS $$
BEGIN
    -- "NULLs shall not pass!" - Gandalf, probably
    IF instr IS NOT NULL THEN
        IF acc = '' THEN
            RETURN instr;
        ELSE
            RETURN acc || ', ' || instr;
        END IF;
    END IF;
    RETURN acc;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

SELECT group_column, commacat('', string_column) AS combined_strings
FROM your_table
GROUP BY group_column;


Custom strings with conditional formatting

Need more refined control, say conditional formatting or extra text, on a per-row basis? Craft your bespoke function for the task:

CREATE OR REPLACE FUNCTION add_insight(acc text, next_val text, condition boolean) RETURNS text AS $$
BEGIN
    -- Like an SQL chameleon, change colors based on condition!
    IF condition THEN
        return acc || next_val || ' (important!)';
    ELSE
        return acc || next_val;
    END IF;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- Behold, the SQL chameleon in action:
SELECT group_column, add_insight('', string_column, condition) AS combined_strings
FROM your_table
GROUP BY group_column;


Lurking in the legacy code jungle, bump into older PostgreSQL versions and survive with a CREATE AGGREGATE function paired with textcat for custom concatenation rules.

Scaling performance and adaptability with PostgreSQL aggregates

Averting performance pitfalls

In PostgreSQL 9.0+, STRING_AGG shines, avoiding resource-draining array operations induced by array_agg and array_to_string.

Code cleanups for readability

Transforming chunks of SQL into a readable orchestra of commands with STRING_AGG fundamentally improves code maintainability - something your future self will thank you for.

Adapting to diverse database environments

Combining a strong grasp of both classic and cutting-edge SQL techniques equips you well for everything from age-old databases teeming with legacy code to the sleekest modern DBMS setups.