PostgreSQL offers various built-in string manipulation functions. One such function is STRING_AGG() which comes up with the ease and efficiency of aggregating multiple strings into a single. It is one of the aggregate functions that Postgres supports and is widely used to concatenate the list of strings.

This blog will explain what exactly the STRING_AGG() is and how to use it in Postgres. For this purpose, the below-listed concepts will be covered in this post:

- How to Use STRING_AGG() Function in Postgres?
- Generating a Comma-separated List of Values Using STRING_AGG() Function.
- Generating a Comma-Separated List of Values From Multiple Columns.

How to Use STRING_AGG() Function in Postgres?

The STRING_AGG() function in Postgres is used to concatenate multiple strings into a single string, separated by a specific delimiter/separator. The syntax for using STRING_AGG() is:

STRING_AGG(col_name/expression, delimiter[order_by_clause]);

Here, in the above syntax,

- The “col_name/expression” represents the column whose values you want to concatenate.
- The delimiter represents the separator between each value, such as “,”, “-”, etc.
- The “order_by_clause” specifies the order in which the values should be aggregated.

Generating a Comma-separated List of Values Using STRING_AGG() Function

To generate a comma-separated list of values, you need to pass an expression and a comma as arguments to the STRING_AGG() function.

Example: How to Use STRING_AGG() Function on a Single Column?

We have created a sample table named “staff_info” that contains the following data:

Let’s use the STRING_AGG() function to generate a list of comma-separated values for the “staff_info” table:

SELECT staff_designation, STRING_AGG(staff_name, ',')
FROM staff_info 
GROUP BY staff_designation;

The above example uses the STRING_AGG() function to get a comma-separated list of employee names. The GROUP BY clause is used to group the data with respect to designation:

The output proves that the STRING_AGG() function retrieves the comma-separated list of employees’ names.

Generating a Comma-Separated List of Values From Multiple Columns

The STRING_AGG() function accepts only two arguments, an expression, and a separator. So if you want to aggregate multiple columns using STRING_AGG(), then you must concatenate the column names in the first argument and specify the separator in the second argument. Use the concatenation symbol “||” to concatenate various columns. Alternatively, multiple STRING_AGG() functions can be used in a single query to generate a comma-separated list of values from various columns.

Example 1: STRING_AGG() Function on Multiple Columns Using Concatenation Operator

We have created two sample tables named “employee_info” and “department_info”, whose details are shown in the following snippets:

The department_info table contains the following data:

Now we will learn how to use the STRING_AGG() function to multiple columns:

SELECT dpt_name,
STRING_AGG (e_id ||' ' || e_name, ', ') AS emp_details
FROM employee_info
INNER JOIN department_info USING (e_id)
GROUP BY dpt_name;

In the above code:

- Multiple columns are passed as the first argument to the STRING_AGG() function.
- The concatenation symbol combines multiple columns, and the comma “,” is used as a separator/delimiter.
- The INNER JOIN joins the employee_info and department_info tables.
- The GROUP BY clause groups the table’s data with respect to the department name.

This is how you can use the STRING_AGG() function to generate a list of comma-separated values from different columns.

Example 2: Using Multiple STRING_AGG() Functions on Multiple Columns

In the following example, we will show you how to use multiple STRING_AGG() functions on multiple tables to generate a list of comma-separated values from different columns:

SELECT dpt_name,
STRING_AGG (e_name, ', '), STRING_AGG (e_email, ', ')
FROM employee_info
INNER JOIN department_info USING (e_id)
GROUP BY dpt_name;

The output authenticates the working of the STRING_AGG() function.

Conclusion

PostgreSQL offers a STRING_AGG() function, which comes up with the ease and efficiency of aggregating multiple strings into one. It accepts two arguments: an expression(list of strings) and a separator; consequently, it concatenates the given set of strings into a single string, separated by a specified delimiter/separator. This post explained the usage of the STRING_AGG() function with suitable examples.