PostgreSQL group_concat Example

In this PostgreSQL tutorial, I will show you PostgreSQL group_concat Examples. I will use the different functions in place of group_concat to get a string of values separated by a separator.

PostgreSQL group_concat Example

With the Examples, I will show you how to use the STRING_AGG() function and combine the ARRAY_TO_STRING() and ARRAY_AGG() functions to achieve a similar result to the GROUP_CONCAT function.

Example 1: Using STRING_AGG

The group_concat function returns a result that contains a string value separated by a separator, such as a comma. However, this function only exists in MySQL and other database applications, and doesn’t exist in PostgreSQL.

But to perform the same operation or tasks as group_concat(), PostgreSQL has a function called STRING_AGG().

Syntax

The syntax is given below.

string_agg(text|column, delimiter)

The string_agg() function takes two values: a string or column, and a separator or delimiter to separate the values.

Let’s check with an example of how the string_agg() function works.

Consider a table ’employees’ with columns ‘department’, ’employee_name’, and ‘salary’ as shown in the picture below.

Now pass the column ’employee_name’ and delimiter as ‘,’ comma to the function string_agg() to get the result that will contain the list of employees’ names, each separated by a comma. Use the code below.

SELECT STRING_AGG(employee_name, ',') 
    FROM employees;

After executing the above query, I got the expected output as shown in the screenshot below.

Look at the above picture; the query returns a list of employees’ names separated by a comma, with the value (David, Charlie, James, Bruce, Adam).

You can also use a different separator, such as the pipe |, in the same query and see the result.

SELECT STRING_AGG(employee_name, '|') 
    FROM employees;

As shown in the screenshot below, I obtained the expected output after executing the query above.

Again, the list of employees’ names is separated by the pipe | symbol as (David|Charlie|James|Bruce|Adam).

Example 2: With multiple columns

To use the multiple columns within the function STRING_AGG(), you can use the double || operator to separate the columns.

Let’s take an example, I will use the same table ’employees’ that is used in the above topics. Suppose you want to retrieve a list of employees’ names along with their salaries, you can use the following query.

SELECT STRING_AGG(employee_name || salary, '-') 
    FROM employees;

Check out the screenshot below; I obtained the expected output after executing the query above.

If you look at the query part ‘STRING_AGG(employee_name || salary, ‘-‘)’, the two columns employee_name and salary are passed to the function with the pipe || operator, which concatenates the value from the columns of both tables into a single string value.

Also, here a different separator hyphen (-) is used as the second value to the function to separate the list of values.

Example 3: Using Array_agg and Array_to_string

So far, you have learned how to use the STRING_AGG() function, which works similarly to the MySQL group_concat() function. However, PostgreSQL has two functions, array_agg() and array_to_string(), which, when combined, work similarly to the group_concat() function.

First, I will demonstrate how these functions work individually using an example.

array_to_string(): This function takes the array values with a separator and returns the result containing the string separated by the separator. Check the example below.

SELECT array_to_string(array[2, 6 , 8], ',');

After executing the above query, I obtained the expected output, as shown in the screenshot below.

Look how the function array_to_string(array[2, 6, 8], ‘,’) converted the given array “array[2, 6, 8]” with separator as comma ‘,’ to string value ‘2, 6, 8′.

array_agg(): This function takes multiple values as input and converts them into array values. See for an example of how the array_agg() function works.

SELECT array_agg(employee_name) FROM employees;

After executing the above query, I got the expected output as per the below screenshot.

The above function array_agg(employee_name) returns the array containing the values of column employee_name.

Now you understand that how these functions work, let’s take an example by combining these functions together to work like the group_concat() function.

SELECT array_to_string(array_agg(employee_name), ',') 
    FROM employees;

After executing the above query, I got the output as expected. Check out the below screenshot for your reference.

If you look at query part “array_to_string(array_agg(employee_name), ‘,’)”, the function array_agg(employee_name) is passed as the first value to that function and the second value as the separator comma ‘,’ to convert it into string values separated by a comma.

Conclusion

In this PostgreSQL tutorial, you have learned how to use the STRING_AGG() function to get string values separated by a delimiter and learned this function is similar to group_concat. Also done some examples using the functions ARRAY_TO_STRING() and ARRAY_AGG() which work like the group_concat function when combined together.

You may also like:

• Postgresql Concat + Examples
• PostgreSQL Like Operator
• PostgreSQL now function
• Postgres Min Function