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:
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:
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:
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:
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:
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:
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:
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:
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.