How to Concatenate Data in PostgreSQL: A string_agg() Guide?

Mastering Data Concatenation in PostgreSQL with string_agg()

In database management, combining data from multiple rows into a single string is a common task. While MySQL offers the convenient GROUP_CONCAT function, PostgreSQL provides the equally powerful string_agg() function. This guide explains how to use string_agg() to achieve the same result.

The PostgreSQL Solution: string_agg()

Before PostgreSQL 9.0, a direct equivalent to GROUP_CONCAT was unavailable. However, string_agg() fills this gap, efficiently concatenating values from a column with a user-specified delimiter.

Syntax and Usage

The string_agg() function follows this simple syntax:

string_agg(expression, delimiter)

• expression: The column or expression to be concatenated.
• delimiter: The separator between concatenated values (defaults to a comma if omitted).

Illustrative Example

Let's assume a table as described in the original question. The following query demonstrates string_agg() in action:

SELECT id, 
       string_agg(column_name, ',') AS concatenated_values
FROM the_table
GROUP BY id;

This query would yield the following output:

<code>TM67 | 4,9,72
TM99 | 2,3</code>

Key Considerations

• string_agg() works with any expression, not just columns.
• The delimiter is optional; a comma is used by default.
• To avoid duplicate values in the concatenated string, use the DISTINCT keyword within the string_agg() function.

This guide provides a clear and concise method for concatenating data in PostgreSQL, offering a robust alternative to MySQL's GROUP_CONCAT.

The above is the detailed content of How to Concatenate Data in PostgreSQL: A string_agg() Guide?. For more information, please follow other related articles on the PHP Chinese website!