How to use Postgresql group_concat

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

Also, I will show you how to use STRING_AGG() function and combine the functions ARRAY_TO_STRING() and ARRAY_AGG() to work like the group_concat function.

How to use Postgresql group_concat using STRING_AGG

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

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

The syntax is given below.

string_agg(text|column, delimiter)

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

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 below picture.

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

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

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

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

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

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

How to use Postgresql group_concat 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 get the list of employees’ names with salary, you can use the below query.

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

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 pip || operator which concatenates the value from the columns of both tables into a single string value.

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

Postgresql group_concat using Array_agg and Array_to_string

Till now you have learned how to use the STRING_AGG() function which works like the group_concat() of MySQL. But PostgreSQL has two functions array_agg() and array_to_string(), when these two functions combine together, they work like the group_concat() function.

First, I will show how these functions work one by one using an example.

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

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

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;

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;

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