Understanding the CONCAT Function in PostgreSQL

Introduction

If you’re working with data in PostgreSQL, you’ll often find it necessary to combine different data to create more readable results. The PostgreSQL CONCAT function offers a simple and powerful way to accomplish this task. This article will explain how this function works and show some examples of performing concatenation in PostgreSQL.

Prerequisites

In order to follow along with the examples shown in this tutorial, you’ll need to have PostgreSQL installed and configured on your machine. Be sure the process is running in the background. If you’re a Linux or Windows user who needs to install PostgreSQL, you can download it here.

CONCAT Function in PostgreSQL

PostgreSQL version 9.1 provides the CONCAT string function, which enables us to concatenate two or more strings into a single combined string.

Shown below is the basic syntax for the PostgreSQL CONCAT function:

CONCAT string_1, string_2, string_3,...

As you can see, the function accepts a series of arguments that are of a string type: char, text or varchar.

The CONCAT function will convert NULL values to empty strings. Let’s look at two examples– the first SELECT statement will not contain any NULL values, but the second one will:

SELECT CONCAT ('concatenation',' ','example') AS the_result_string;

The output from this example will look like the following:

the_result_string
-----------------------
concatenation example
(1 ROW)

Now, let’s try to concatenate a NULL value to a string using the same syntax:

SELECT CONCAT ('concatenation',' ',NULL) AS the_result_string;

The output of this statement is shown below:

the_result_string
-------------------
concatenation
(1 ROW)

We can see in the output that the CONCAT function converted the NULL value into an empty string and then proceeded with the concatenation process.

Using the CONCAT Function in PostgreSQL

Let’s take a look at the player table shown below, which contains the following records. We want to concatenate the details of every record in order to present it in a more readable format:

player_id | player_name | playing_hours | rating | rank
-----------+--------------+---------------+--------+------
1 | teamsolo | 200 | 5 | 1
2 | armada | 230 | 5 | 1
3 | lonewolf | 230 | 4 | 1
4 | knownsecret | 230 | 4 | 2
5 | winterwolf | 230 | 4 | 2
6 | moltenice | 230 | 4 | 2
7 | silentkiller | 240 | 3 | 3
8 | casper | 240 | 3 | 3

The following SELECT statement concatenates the specified arguments using the CONCAT function:

SELECT
player_name, playing_hours,
CONCAT (
'Player Name:',
player_name,
' Rank No. of:',
rank

)
FROM
player;

The output of this SELECT statement will look like this:

player_name | playing_hours | concat
--------------+---------------+----------------------------------------
teamsolo | 200 | Player Name:teamsolo Rank No. OF:1
armada | 230 | Player Name:armada Rank No. OF:1
lonewolf | 230 | Player Name:lonewolf Rank No. OF:1
knownsecret | 230 | Player Name:knownsecret Rank No. OF:2
winterwolf | 230 | Player Name:winterwolf Rank No. OF:2
moltenice | 230 | Player Name:moltenice Rank No. OF:2
silentkiller | 240 | Player Name:silentkiller Rank No. OF:3
casper | 240 | Player Name:casper Rank No. OF:3
(8 ROWS)

Double Pipe (||) Operator in PostgreSQL

The PostgreSQL double pipe (||) operator can also be used to concatenate two or more strings. Shown below is the basic syntax for using the PostgreSQL double pipe operator:

SELECT 'concatenation' || ' ' || 'example' AS the_result_string;

Let’s see what the output of this statement looks like:

the_result_string
-----------------------
concatenation example
(1 ROW)

Next, we’ll try to concatenate a NULL value to a string, using the same syntax.

SELECT 'concatenation' || ' ' || 'null' AS the_result_string;

The output is shown below:

the_result_string
--------------------
concatenation NULL
(1 ROW)

You can see that this statement returns a NULL value. This shows that the || operator behaves differently than the CONCAT string function, which converts a NULL value to an empty string.

CONCAT_WS function in PostgreSQL

PostgreSQL also provides another CONCAT-like function called the CONCAT_WS function. This function concatenates a series of strings into a single string separated by a specified operator; hence, the “WS” in the function name, which stands for “with separator”.

Like the standard CONCAT function, this function also ignores NULL values.

The following shows the basic form of the CONCAT_WS function:

CONCAT_WS(the_separator,string_1,string_2,...);

In this syntax, the_separator represents the string that will serve as a separator between each of the specified arguments within the function. The string_1, string_2 arguments represent the strings to be concatenated.

Using CONCAT_WS Function in PostgreSQL

Now that we understand how the CONCAT_WS function works in PostgreSQL, let’s look at a real-life example of its use. We’ll use the same player table from the previous section.

We use the following SELECT statement:

SELECT
concat_ws (', ', player_name, rank) AS player_details
FROM
player
ORDER BY
rating;

The result should look something like this:

player_details
-----------------
silentkiller, 3
casper, 3
knownsecret, 2
moltenice, 2
winterwolf, 2
lonewolf, 1
teamsolo, 1
armada, 1
(8 ROWS)

Conclusion

There’s no doubt that being able to concatenate strings can transform the output of a database query, making the results clearer and more readable. In this tutorial, we learned how to use both the PostgreSQL CONCAT and CONCAT_WS functions. With the various examples we provided to use as a guide, you’ll be able to use these functions to improve the appearance of results from your own database queries.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.