How to Concatenate Strings Using STRING_AGG?

When dealing with database queries, there’s often a need to aggregate and concatenate strings from multiple rows into a single, comma-separated string. PostgreSQL’s STRING_AGG function addresses this need, providing a robust tool for string manipulation The STRING_AGG is an aggregation function that is used to concat values from various records into one single value. Since it is an aggregating function it is used with a GROUP BY clause.

Syntax:

SELECT col1, col2, ..., coln,
STRING_AGG ( col_name, str_val [ORDER BY clause] )
FROM table GROUP BY col1, col2, ..., coln;

Explanation:

• col1, col2,coln: The columns which will be used to group the records.
• col_name: The name of the column whose value needs to be concatenated.
• clause: The optional clause which can be used to order the data before concatenation.
• str_val: The optional separator value.
• table: The table from which to aggregate the results

let’s Setting Up Environment for Concatenate Strings

We will create a table called test and insert some sample values in the table. The following code creates the test table and inserts some entries into it.

CREATE TABLE test (
  id INTEGER PRIMARY KEY,
  val1 VARCHAR(20),
  val2 VARCHAR(20)
);

INSERT INTO test VALUES (21, 'val1', '32');
INSERT INTO test VALUES (11, 'val2', '90');
INSERT INTO test VALUES (90, 'val1', '18');
INSERT INTO test VALUES (77, 'val1', '65');
INSERT INTO test VALUES (43, 'val3', '20');
INSERT INTO test VALUES (81, 'val3', '88');
INSERT INTO test VALUES (29, 'val2', '72');
INSERT INTO test VALUES (55, 'val2', '47');
INSERT INTO test VALUES (72, 'val3', '11');

Output:

Explanation: Now that we have the table in place, lets go through the STRING_AGG() function.

In database management, aggregating and concatenating strings is a common requirement. PostgreSQL provides a powerful solution for this with the STRING_AGG function. This article explores how to leverage STRING_AGG to concatenate strings in PostgreSQL efficiently, offering multiple approaches to cater to various situations. In this article, we will understand how to use STRING_AGG effectively with the help of various examples and so on.