In PostgreSQL, concatenation is an important concept that allows the users to join two or more words, sentences, strings, etc. It is possible through the “||” operator and the “CONCAT()” function. This article demonstrates syntax along with practical examples to cover all aspects of the “||” operator and “CONCAT()” function to concatenate multiple strings. The below concepts will be explained in this Postgres guide:

• How to Concatenate Strings in PostgreSQL
• Example 1: Concatenate Two Strings Using || Operator in Postgres
• Example 2: Concatenate Two Strings and a NULL Value Using || Operator
• Example 3: Concatenate Two Strings Using CONCAT() Function
• Example 4: Concatenate Two Strings and a NULL Value Using CONCAT() Function

Let’s begin!

How to Concatenate Strings in PostgreSQL

In this section, we will show you how to concatenate strings using the “||” operator and the CONCAT() function. Let's start with the syntax of the “||” operator:

'str_1' || 'str_2 ' || 'str_n';

Here, str_1, str_2, str_n are the strings to be concatenated.

The syntax to concatenate two or more strings using the “CONCAT()” function will be as follows:

SELECT CONCAT('str_1', 'str_2', 'str_n');

In the above syntax, str_1, str_2, str_n represents the strings, words, characters to be concatenated.

Example 1: Concatenate Two Strings Using || Operator in Postgres

In PostgreSQL, two strings can be concatenated with the help of the “||” operator. For this, two strings “PostgreSQL” and “Databases” will be concatenated through the “||” operator:

SELECT 'PostgreSQL' || ' ' || 'Databases' AS result;

The output shows that the “||” operator successfully concatenated the given strings.

Example 2: Concatenate Two Strings and a NULL Value Using || Operator

Let’s consider the below snippet to see how the “||” operator deals with the NULL values:

SELECT 'PostgreSQL' || NULL || 'Databases' AS output;

Output proves that concatenating a NULL value with strings using the "||" operator returns null. In simple terms, we can say that the “||” operator retrieves faulty(NULL) results while concatenating strings with a NULL value.

Example 3: Concatenate Two Strings Using CONCAT() Function

An example is considered to concatenate two strings through the “CONCAT()” function:

SELECT CONCAT ('Welcome',' ', 'PostgreSQL');

Users can verify that two strings “Welcome” and “PostgreSQL” have been concatenated as “Welcome PostgreSQL” in the above figure.

Example 4: Concatenate Two Strings and a NULL Value Using CONCAT() Function

Let’s concatenate a couple of strings, i.e., “Harry”, “Peter” and a NULL value using the CONCAT() function:

SELECT CONCAT('Harry', NULL, 'Peter');

Users can verify that “HarryPeter” has been concatenated without any interruption of the NULL value.

Example 5: Concatenate Two Columns of a Table in PostgreSQL

Let’s concatenate the values of the table’s columns using the CONCAT() function. For this purpose, an existing table “candidates” is utilized as follows:

SELECT * FROM candidates;

The “candidates” table has multiple columns including “candidate_id”, “first_name”, “last_name” and “email”.

To concat “first_name” and “last_name” column values, we will execute the following statement:

SELECT first_name, last_name, 
CONCAT(first_name,' ' , last_name) "Full Name" 
FROM candidates;

Users can verify that “Joe” and “Com” have been concatenated as “Joe Com” in the “Full Name” column.

Great Job! You have learned the usage of the “CONCAT” function in this PostgreSQL tutorial.

Conclusion

In PostgreSQL, the “||” operator and a built-in function named “CONCAT()” are used to concatenate multiple strings, characters, etc. The “||” operator retrieves faulty results while concatenating the strings with the NULL values. However, the CONCAT() function handles the NULL values appropriately. This article has covered all possible aspects of concatenating strings in PostgreSQL.