PostgreSQL

Postgres String agg

In PostgreSQL, there are built-in aggregate functions including SUM, COUNT, ARRAY_AGG ( ), STRING_AGG ( ), AVG ( ), MAX, MIN etc. The purpose of using aggregate functions in your code is to determine output from a set of values. In this article, we will be discussing the STRING_AGG ( ) function. The STRING_AGG ( ) is an aggregate function comprised of two parameters; the first parameter takes input values and concatenates the values into strings. In the second parameter, you specify the delimiter which separates the first parameter.

The STRING_AGG ( ) function and ARRAY_AGG ( ) functions working is same except that STRING_AGG ( ) is the aggregate function that returns the result as string type while the ARRAY_AGG ( ) function returns you the array type as results.

This article is a complete guide to explaining STRING_AGG ( ) aggregate function in PostgreSQL. You will be witnessing various examples of the STRING_AGG ( ) function in PostgreSQL with a detailed explanation. The following syntax will help you get the basic understanding of using the STRING AGG( ) function in PostgreSQL:

STRING_AGG ( value , delimiter )

In the above syntax,

  • First, write the STRING_AGG( ) function with brackets ( ). The SRING_AGG( ) function takes two parameters.
  • In the first parameter, the ‘value’ is any text value that is a string character. If you input any other data type than string character, it would not take that value as input.
  • In the second parameter, the ‘delimiter’ separates the concatenated strings.

The STRING_AGG( ) also takes the ORDER BY clause in the statement. It is optional if you want the output to be in a specific order, then you can use the ORDER BY clause with the STRING_AGG( ) function. Below is an example of using the ORDER BY clause in the statement:

STRING_AGG ( value , delimiter [ ORDER_BY_CLAUSE ] )

You can add the specified condition in the ‘ ORDER_BY_CLAUSE ‘ using the ORDER BY function in the statement like that:

ORDER BY value1 ASC | DESC , ...

The ORDER BY function can be stated as either in ascending or descending form in the statement according to the value (value1) written.

You can also use the GROUP BY clause in the statements; it tells the rows that are grouped in the table.

STRING_AGG ( ) Function Examples in PostgreSQL:

To use STRING_AGG ( ) function in the statements you first need to create a sample table for examples in PostgreSQL. Below is the code for creating a sample table in PostgreSQL:

CREATE TABLE players (

player_name TEXT ,

team_name TEXT ,

player_positon TEXT

) ;

Text Description automatically generated

The above syntax will create a table in the database with the name “players” and columns as player_name, team_name, and player_position. The PostgreSQL provides the following message:

Text Description automatically generated with medium confidence

To verify that the table is created with the columns specified, run the SELECT query below:

SELECT * FROM "players" ;

Graphical user interface, text Description automatically generated

Now, insert some values in the table “players” with the INSER INTO command:

INSERT INTO "players"

VALUES ( 'Chris', 'West Indies', 'Batsman' ), ( 'Shannon', 'West Indies', 'Bowler'),

('Bravo', 'West Indies', 'Batsman');

INSERT INTO "players"

VALUES ( 'James', 'New Zealand', 'All rounder' );

INSERT INTO "players"

VALUES ( ‘Shaheen’, ‘Pakistan’, ‘Bowler’ ), ( ‘Sarfaraz’, ‘Pakistan’, ‘Wicket keeper’ ) ;
Text Description automatically generated

The above values are inserted into the table “players” using the INSERT INTO and VALUES command in the statements, and we can confirm that by executing the SELECT statement below:

SELECT * FROM "players" ;

A screenshot of a computer screen Description automatically generated with medium confidence

The values that were specified in the INSERT INTO command are now executed successfully and can be seen in the above output.

Generating Comma-Separated Values Using STRING_AGG( ) in PostgreSQL:

To generate a list of values separated by commas we will use STRING_AGG ( ) function in the statement. Below is the statement to generate values separated by commas:

SELECT "team_name",string_agg("player_name", ',' )

FROM "players" GROUP BY "team_name" ;

We have used the STRING_AGG( ) function to separate the “player_name” column with commas in the SELECT statement. In the first parameter of the STRING_AGG( ) the expression is defined which we want to separate; in the second parameter, we have used comma ‘ , ’ as a delimiter or separator to separate the values. We have used the GROUP BY command to divide the rows with respect to the column “team_name”. The output for the above syntax is displayed as:

A screenshot of a computer Description automatically generated with low confidence

The values of “player_name” are concatenated by commas in the above output and displayed with the “team_name”.

Separating Multiple Columns with Commas Using STRING_AGG( ) in PostgreSQL:

We can use more than one STRING_AGG( ) function in the single SELECT statement. The below example shows the demonstration of that:

SELECT "team_name",string_agg("player_name",', '), string_agg("player_positon",',')

FROM "players" GROUP BY "team_name";

I have used two STRING_AGG( ) functions in two different columns to concatenate the values of the columns by comma-separators. The above statement shows the following results:

A screenshot of a computer screen Description automatically generated with medium confidence

It is visible that two-column values are now separated by commas and generated the list.

We don’t know the column names in the above output they are mentioned as default “string_agg” names, we can specify the column names using AS function in the above statement. This is the syntax for using AS function and specifying names to column:

SELECT "team_name",string_agg ("player_name", ',') AS players_name,

string_agg ("player_positon", ',') AS players_positions

FROM "players" GROUP BY "team_name";

In the above statement, with the STRING_AGG( ) function, I have specified the AS keyword with the desired name, displaying a name in the column. We can see in the below output:

A screenshot of a computer screen Description automatically generated with medium confidence

The columns that were first by default named as “string_agg” are now named as “players_name” and “players_positions” respectively.

Conclusion:

We have learned in this article about the STRING_AGG( ) function in PostgreSQL, its definition, its use, and implementation. The STRING_AGG( ) function can be used to generate comma-separated values with single and multiple columns as well. The AS keyword can be used to display the specific names to selected columns.

About the author

Saeed Raza

Hello geeks! I am here to guide you about your tech-related issues. My expertise revolves around Linux, Databases & Programming. Additionally, I am practicing law in Pakistan. Cheers to all of you.