Understanding the CONCAT Function in PostgreSQL
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.
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:
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:
The output from this example will look like the following:
Now, let’s try to concatenate a NULL value to a string using the same syntax:
The output of this statement is shown below:
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:
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:
' Rank No. of:',
The output of this SELECT statement will look like this:
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
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:
Let’s see what the output of this statement looks like:
Next, we’ll try to concatenate a NULL value to a string, using the same syntax.
The output is shown below:
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:
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:
concat_ws (', ', player_name, rank) AS player_details
The result should look something like this:
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