However, PostgreSQL does not natively support the group_concat() function unlike MySQL. Therefore, this tutorial explores how we can achieve a similar functionality using the string_agg() function.
PostgreSQL String_Agg Function
The string_agg function in PostgreSQL allows us to concatenate the values from multiple rows into a single string and is separated by the specified parameter.
The function syntax usage is as demonstrated as follows:
FROM table_name
WHERE conditions
GROUP BY grouping_columns;
The following syntax is as expressed as follows:
column_name – It specifies the name of the column whose column we wish to concatenate.
delimiter – It defines the separator character that is used when joining the input values.
table_name – The target table that contains the data.
grouping_columns – It specifies the columns that are used to group the specified data.
PostgreSQL String_Agg Function Example
Let us take a more practical example to illustrate how the function works. Suppose we have a table which contains the student information. The table contains three columns: id, name, and subject.
If we wish to concatenate the students’ names who enrolled in the same subject, we can use the string_agg function.
FROM students
GROUP BY subject;
Once we run the given query, it should return a result set with two main columns: the subject and the students. The students contain the concatenated names of students for each subject and are separated by a comma.
NOTE: The string_agg function sorts the concatenated values by default. You can add an ORDER BY clause inside the string_agg function to maintain the original order.
There you have it! A straightforward and efficient method of achieving a similar functionality provided by the group_concat() function in PostgreSQL.
Conclusion
In this brief but effective tutorial, we learned how to use the string_agg function in PostgreSQL to achieve a similar functionality provided by the group_concat() function in MySQL.
