Complete Guide to String Aggregation in PostgreSQL: From GROUP BY to STRING_AGG

Introduction

In database queries, there is often a need to concatenate string fields after grouping. This requirement is particularly common in scenarios such as report generation and data summarization. PostgreSQL, as a powerful open-source relational database, provides multiple methods for implementing string aggregation.

PostgreSQL 9.0 and Later Versions

Starting from PostgreSQL 9.0, the system includes the built-in string_agg(expression, delimiter) function, which is the preferred method for string aggregation. This function accepts two parameters: the expression to aggregate and the delimiter.

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

In PostgreSQL 9.0, the ability to specify sorting order within aggregate expressions was also introduced, which is crucial for ensuring result consistency:

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

PostgreSQL 8.4 Version

For PostgreSQL 8.4 users, although support for this version ended in 2014, similar functionality can be achieved by combining the array_agg and array_to_string functions:

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

This approach first collects values into an array and then converts the array to a string, connecting elements with the specified delimiter.

Custom Implementations for Earlier Versions

In PostgreSQL 8.3 and earlier versions, there were no built-in string aggregation functions, requiring custom implementations. The most basic method involves creating a custom aggregate using the built-in textcat function:

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

However, this method does not automatically add separators. To achieve comma-separated results, more complex custom functions are needed:

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;

This function adds comma separators between non-null values but retains extra commas when encountering null values. To handle null cases, a more robust version can be created:

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;

Performance Analysis and Best Practices

In practical applications, the string_agg function typically offers the best performance as it is specifically optimized for string aggregation. For processing large datasets, it is recommended to:

• Use the latest supported PostgreSQL version
• Specify sorting order where possible to ensure result consistency
• Consider memory usage, especially when handling large strings
• Test the performance of different methods to find the most suitable solution for specific scenarios

Practical Application Scenarios

String aggregation has important applications in various business scenarios:

• Generating mailing lists by combining all recipient names at the same address
• Creating reports that summarize all items under the same category
• Data export by merging related records into single fields
• Log analysis by aggregating multiple instances of the same event

By appropriately utilizing PostgreSQL's string aggregation capabilities, data processing workflows can be significantly simplified, improving development efficiency.