How to Use STRING_AGG to Concatenate Strings in PostgreSQL?

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.

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:

Table Created

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

Examples of STRING_AGG Function to Concatenate Strings

Example 1: Group According to Values

The following query groups the table according to the values in the field ‘val1’ and then uses STRING_AGG() to concat the values of ‘val2‘.

Query:

SELECT val1, STRING_AGG(val2, ',') as val2
FROM test
GROUP BY val1;

Output:

Output

Explanation: This query groups rows from the test table by the val1 column and concatenates values from the val2 column for each group and separated by commas. The result set includes unique values from val1 and their concatenated val2 values.

Example 2: Group According to Values in Ascending Order

The following query is similar to the query performed in the above examples. The only difference is that it orders the values in ‘val2’ in ascending order by making use of the optional ORDER BY clause before concatenating.

Query:

SELECT val1, STRING_AGG(val2, ','  ORDER BY CAST(val2 AS INT)) as val2
FROM test
GROUP BY val1;

Output:

Output

Explanation: This query is similar to the previous one, but it adds an ORDER BY clause within the STRING_AGG function. It first converts the val2 values to integers using CAST and then orders them in ascending order before concatenating them. The result set will contain val1 values and their val2 values concatenated and sorted numerically, separated by commas.

Example 3: Group the Data and Concatenates Values

Like the query presented in example 1, The following query groups the data by ‘val1’ and concatenates values of ‘val2’. The only difference it is that it uses ‘/’ as the separator.

Query:

SELECT val1, STRING_AGG(val2, '/') as val2
FROM test
GROUP BY val1;

Output:

Output

Explanation: This query groups rows from the test table by the val1 column and concatenates values from the val2 column for each group, separated by slashes (/). The result set includes unique values from val1 and their concatenated val2 values.

More Examples to Concatenate Strings

Let’s understand through the technical example. Let’s create the table and insert some data inside it.

Query:

CREATE TABLE language (
  state VARCHAR(50),
  name VARCHAR(50)
);

INSERT INTO language VALUES ('Bihar', 'Hindi');
INSERT INTO language VALUES ('Punjab', 'Punjabi');
INSERT INTO language VALUES ('Assam', 'Assamese');
INSERT INTO language VALUES ('Punjab', 'Urdu');
INSERT INTO language VALUES ('Bihar', 'English');
INSERT INTO language VALUES ('Bihar', 'Maithili');
INSERT INTO language VALUES ('Punjab', 'Hindi');
INSERT INTO language VALUES ('Assam', 'Bengali');
INSERT INTO language VALUES ('Maharashtra', 'Marathi');
INSERT INTO language VALUES ('Gujarat', 'Gujarati');
INSERT INTO language VALUES ('Maharashtra', 'Hindi');
INSERT INTO language VALUES ('Maharashtra', 'English');
INSERT INTO language VALUES ('Assam', 'Bodo');

Output:

Output

Explanation: As we can see in the image, the above table contains the various languages spoken in various states of India.

Let’s use STRING_AGG() function to concat the different languages spoken in a particular state. First we will start by the vanilla version of the query to use STRING_AGG. We will group using the state column and concat the values in the name column.

Query:

SELECT state, STRING_AGG(name, ',') as different_languages
FROM language
GROUP BY state;

Output:

Output

Explanation: This query groups rows from the language table by the state column and concatenates values from the name column for each group, separated by commas. The result set includes unique values from state and their concatenated name values, representing different languages spoken in each state.

Now we will make use of ORDER BY clause to order the different languages in ascending order.

Query:

SELECT state, STRING_AGG(name, ','  ORDER BY name) as different_languages
FROM language
GROUP BY state;

Output:

Output

Explanation: This query is similar to the previous one, but it adds an ORDER BY clause within the STRING_AGG function. It concatenates values from the name column for each group, orders them alphabetically by name, and separates them by commas. The result set includes unique values from state and their concatenated

We can even use the second argument of the function to define a custom separator to concat the different languages.

Query:

SELECT state, STRING_AGG(name, ';') as different_languages
FROM language
GROUP BY state;

Output:

Output

Explanation: This query groups rows from the language table by the state column and concatenates values from the name column for each group, separated by semicolons (;). The result set includes unique values from state and their concatenated name values, representing different languages spoken in each state, separated by semicolons.

Conclusion

In this article, we covered how we can make use of STRING_AGG function to concatenate strings in MariaDB. We started by looking at what STRING_AGG is, and then we looked at several examples. In the examples, we made use of the vanilla version, ORDER BY clause and even provided a custom separator value. Finally, we also saw how we can use the concepts we learned in this article to a real-life situation through the technical example.