Postgresql group_concat + Examples

In this PostgreSQL tutorial, we are going to learn about “Postgresql group_concat” which helps in concatenating value from multiple records into one field. It is an aggregate function that returns a string value.

• Postgresql group_concat string_agg
• Postgresql group_concat separator
• Postgresql group concatenate
• Postgres group by concat array
• Postgresql group_concat order by
• Postgres group by concat column

In PostgreSQL, the Group_concat function is not available but we will use the same function as array_agg and array_to_string to work like a group_concat function in PostgreSQL.

Syntax:

Select name_of_col_1, name_of_col_2, name_of_col_3, ….., name_of_col_N array_to_string (array_agg (name_of_col), “Value separator”) from name_of_table JOIN condition Group by condition;

• Array_to_string: We can use this function with array_agg function in PostgreSQL. when both the function come together, they work as the group_concat function.
• Array_agg: It is used to retrieve the element from the group of arrays. This function helps to concatenate the column values.
• Value separator: It is used to separate column values by using the separator, we use ( ‘ , ‘ ), ( ‘ ; ‘ ) to separate the column values with the help of Array_to_string and Array_agg function.

Additionally, we will also use the string_agg function to concatenate column values in Postgresql. we are going to use the group by clause with Array_to_string and Array_agg function because without group by clause, it will show some error.

Use the below code to know the need of the GROUP BY clause.

Select fruits_name, array_to_string(array_agg(fruits_name), ',') from fruits_data;

As we can see in the above output, we haven’t used the group by clause, so it is showing the error. Let’s see the with the help of group by clause.

Select fruits_name, array_to_string(array_agg(fruits_name), ',') from fruits_data group by fruits_name;

Now in the above output, it is working with the group by clause.

Read: How to migrate from MySQL to Postgres

Postgresql group_concat string_agg

In this sub-section, we are going to use the String_agg function to combine all expressions of a column into single values.

Let’s create a table named PersonConcateTable and insert some data into the table.

CREATE TABLE PersonConcateTable(
    FirstName varchar(400) NULL,
    LastName varchar(400) NULL
    );
	
INSERT INTO PersonConcateTable(FirstName,LastName)values('Walter','M. Ashley');
INSERT INTO PersonConcateTable(FirstName,LastName)values('Alex','K. High');
INSERT INTO PersonConcateTable(FirstName,LastName)values('Brian', 'G. Severson');
INSERT INTO PersonConcateTable(FirstName,LastName)values('Eugene','P. Bona');

Now execute the below code to see how the string_agg function is combining the FirstName column into single values.

SELECT STRING_AGG(FirstName,'-') AS Result FROM PersonConcateTable;

In the above output. String_agg function combines the first name of the column into a single value using separator ( – ).

Read: Postgresql listen_addresses

Postgresql group_concat separator

In Postgresql, String_agg function can accept different separator like hyphen ( – ), comma ( , ), etc.

Let’ understand with examples.

Use the below code to separate the first name of PersonConcateTable using comma (,).

SELECT STRING_AGG(FirstName,',') AS Result FROM PersonConcateTable;

We have used the comma (,) to separate the first name of the table named PersonConcateTable as a result, Walter, Alex, Brian, Eugene in the above output.

Now perform the same query to separate the FirstName of the person using the hyphen(-).

SELECT STRING_AGG(FirstName,'-') AS Result FROM PersonConcateTable;

From the above output, we can understand that the different separators can be used like *, $, #, %, @, etc.

Read: Postgresql current_timestamp

Postgresql group concatenate

In Postgresql, we can concate or combine the values from a column into a group of values separated by separator like -,”,%,$, etc.

We are going to use both methods Array_agg, Array_to_string, and String_agg.

Let’s create the dummy table named example.

CREATE TABLE example(table_data VARCHAR);

Insert the following records.

INSERT INTO example(table_data)VALUES('EARTH'),('SKY'),('SPACE'),('CLOUD'),
('SPACE'),('EARTH'),('CLOUD'),('EARTH'),('SKY'),('SPACE'),('CLOUD');

Use the below code to concate the value of the column using Array_agg and Array_to_string functions.

SELECT table_data, array_to_string(array_agg(table_data), '-') FROM example GROUP BY table_data;

Use the code below to concate the value of the column using the String_agg function.

SELECT string_agg(table_data, '@') FROM example GROUP BY table_data;

Read: Postgresql row_number

Postgres group by concat array

In Postgresql, we can Group by the concatenated string values with the help of the string_agg( ) function.

Let’s create the table orderinfo using the below command.

CREATE TABLE orderinfo(id INT,name VARCHAR,order_item VARCHAR);

Enter the following records.

INSERT INTO orderinfo(id,name,oreder_item)VALUES(1,'abc','mango'),
(2,'abc','apple'),(3,'abc','mango'),(4,'abc','mango'),
(5,'xyz','mango'),(5,'xyz','apple'),(7,'xyz','apple'),
(8,'pcq','mango'),(9,'pcq','apple'),(10,'pcq','mango'),
(11,'pqr','apple'),(12,'pqr','apple'),(13,'pqr','mango'),
(14,'abc','mango'),(15,'xyz','mango'),(16,'pqr','mango');

Now group the name of the person using the string_agg function using the below code.

SELECT string_agg(name, ',') AS group_concat FROM orderinfo GROUP BY name;

In the above output, we have concatenated the name of person xyz, abc, pqr, pcq and grouped them by name.

Read: Postgresql auto increment

Postgresql group_concat order by

In Postgresql, we can also order the concatenated string of values but here we are going to use the array_to_string and array_agg function, if you have doubt about these functions, please refer to our introduction section of this tutorial.

Use the below code to order the array of strings according to their names.

SELECT name, array_to_string(array_agg(name), ',') FROM orderinfo GROUP BY name ORDER BY NAME ;

In the above output, the name of person abc, pcq, pqr, xyz is ordered alphabetically with the help of the ORDER BY clause in the above command, By default ORDER BY clause orders the number or string of value in ascending order, It means from lowest to highest or from least value to most value.

Read: PostgreSQL CREATE INDEX

Postgres group by concat column

In this last sub-section of the Postgresq tutorial, we are going to concate two different columns at the same time and group them according to their names.

Use the below code.

SELECT name, array_to_string(array_agg(name), ','),array_to_string(array_agg(order_item), ',') As order_item_name
FROM orderinfo GROUP BY name;

from the above demonstration, we have concatenated the two different columns (name and order_item) values and group them by their names.

This output shows that the item ordered by each person like, pcq first ordered mango then apple so on.

You may also like to read the following tutorials.

• Postgresql import SQL file
• Postgresql generate_series
• Postgresql cast int
• Postgresql REGEXP_REPLACE
• Postgresql date_trunc function
• Postgresql ilike case insensitive
• Postgresql escape single quote
• PostgreSQL Like With Examples

So in this Postgresql tutorial, we have learned about “Postgresql group_concat” which combines several records of a column into a single field. We have covered the following topics.

• Postgresql group_concat string_agg
• Postgresql group_concat separator
• Postgresql group concatenate
• Postgres group by concat array
• Postgresql group_concat order by
• Postgres group by concat column