In relational databases like MySQL, MariaDB, etc. the GROUP_CONCAT() function is used to concatenate the strings from a group to one string or multiple rows into a single field. Although PostgreSQL does not support the GROUP_CONCAT() function, the STRING_AGG() can be used to achieve similar functionality.

This blog post will present a complete guide on GROUP_CONCAT() equivalent in Postgres.

GROUP_CONCAT() Equivalent in Postgres

In PostgreSQL, the STRING_AGG() function can be utilized as an alternative to the GROUP_CONCAT() function. The STRING_AGG() function accepts an expression or strings to be concatenated and a delimiter/separator as arguments and concatenates the given strings using the specified separator:

SELECT STRING_AGG(expression, 'separator') 
FROM tab_name;

Let’s directly dive into the examples to get a profound understanding of the STRING_AGG() function.

Example: MySQL’s GROUP_CONCAT() Equivalent in PostgreSQL

In the following example, we will utilize the STRING_AGG() function on an already created table named “dpt_info”:

SELECT * FROM dpt_info;

We will utilize the STRING_AGG() function on the dpt_name column to get the department names as a comma-separated list:

SELECT STRING_AGG(dpt_name, ',')
FROM dpt_info;

This is how the functionality of the GROUP_CONCAT() can be achieved using the STRING_AGG() in Postgres.

Note: Visit the following guide for a profound understanding of the STRING_AGG() function.

Conclusion

In PostgreSQL, the STRING_AGG() function can be utilized as an alternative to the GROUP_CONCAT() function. The STRING_AGG() function accepts an expression or strings to be concatenated as a first argument and a delimiter/separator as a second argument; as a result, it retrieves a concatenated list of strings separated by a specified delimiter. This post has demonstrated a detailed guide on GROUP_CONCAT() equivalent in Postgres.