How To Concat In PostgreSQL

In this PostgreSQL tutorial, I will show how to use concat in postgresql. Also, you will learn about different functions of PostgreSQL to combine more than one string value into a single value.

How To Concat In PostgreSQL

The full name of the word “concatenate” is concatenate, which means to add something together. In terms of a database, concatenate means joining two things (such as string or text) together to form a single thing( string or text).

There are several ways to concatenate strings or texts in PostgreSQL, and for that, PostgreSQL provides different functions.

In a database scenario, concatenation can be helpful when you want to combine the first or last name of an employee, which is stored in different columns of the table.

Additionally, you can present the information in a more meaningful way by combining the different values into a single value.

Let’s see some approaches that use different functions for concatenating string or text values.

Approach-1: Using Pipe Operator

To concat in PostgreSQL, we can use the pipe || operator.

The pipe || operator of Postgres combines multiple strings or more than one string/texts into a single value. This operator is also called a string concatenation operator.

The syntax is given below.

SELECT string1 || string2 || string3|| .... FROM table_name;

In the above syntax, you can use the pipe || operator to combine multiple strings (string1, string2, and so on) into a single string value. Instead of string, you can also use the columns whose values you want to combine together.

Let’s check with an example, suppose you have a table ‘orders’ with columns first_name, last_name, and order_date.

Now, use the query below to combine the columns first_name and last_name into a single value, which will contain the customer’s first and last name.

SELECT first_name ||' '|| 
    last_name as customer_name, 
    order_date 
FROM orders;

After executing the above query, I got the expected output as shown in the screenshot below.

Look at the output, which shows the customer’s first and last name as a single value or full name in a new column ‘customer_name’. The values of columns first_name and last_name are concatenated using the pipe || operator, as you can see in the above query.

Approach-2: Using CONCAT() function

PostgreSQL has a concat() function that combines multiple strings into a single string value. In general, you pass the list of elements to the concat() function, and these elements should be convertible to a string.

You can provide different types of string data, such as varchar, char, and text. The concat() function is also known as the VARIADIC function, which means it accepts array data as well. If any of the provided input values is NULL, then while concatenating the strings, it ignores the NULL values.

The syntax is given below.

CONCAT(stringValue1, stringValue2, stringValue3, .....)

• CONCAT(): The concat() function itself accepts multiple string data.
• stringValue1, stringValue2, stringValue3, …..: These are the numerous string values that are combined to form a single string value.

Let’s take an example where you have a table called ‘customers_data’ with columns’ name’ and ‘country’.

Now, combine the column’s name and country value into a single value using the below query.

SELECT CONCAT(name ,' lives in ', country) 
FROM customers_data;

After executing the above query, I got the expected output as shown in the screenshot below.

As shown in the above query, the concat() function combines three values: the first is the column ‘name’, the second is the string ‘ lives in ‘, and the third is the column ‘country’.

The query result returns meaningful information, indicating that a customer lives in a specific country, such as ‘John Smith lives in the USA’, and so on.

Approach-3: Using CONCAT_WS() function

The concat_ws() function in PostgreSQL also combines multiple strings into a single value, separated by a specified character. It accepts multiple strings with a separator, which means how you want to separate the combined string; the separator can be a comma (,), colon (:), etc.

The syntax is given below.

CONCAT_WS(sep,stringValue1, stringValue2, stringValue3, .....)

• CONCAT_WS(): It is the concat_WS() function itself that accepts multiple string data with a separator.
• sep: It is a separator value.
• stringValue1, stringValue2, stringValue3, …..: These are the multiple string values that are combined to form a single string value.

Let’s use the same example that you covered in the previous topics.

SELECT CONCAT_WS('- ',name ,' lives in ', country) 
FROM customers_data;

After executing the above query, I got the expected output as shown in the screenshot below.

In the above query, the concat_ws() function contains four values: the first is the separator hyphen (-), the second is the column ‘name’, the third is the string ‘ lives in ‘, and the fourth is the column ‘country’. Then combine them into a single value with a separator hyphen (-), such as ‘Maria Garcia- lives in – Brazil’

In the place of the hyphen, you can use different separators like colon (:), comma (,), asterisk (*) and etc.

Approach-4: Using the FORMAT() Function

We can use the FORMAT() function for this purpose. We can use the below query for this purpose.

SELECT FORMAT('%s %s (%s)', first_name, last_name, email) 
FROM customers;

After executing the above query, I got the expected output as shown in the screenshot below.

The FORMAT() function uses placeholders that are replaced by the values provided:

• %s – String
• %I – SQL identifier
• %L – SQL literal
• %n – Newline

Conclusion

Understanding string concatenation in PostgreSQL is a crucial skill for any database developer or data analyst. We can use the simple double pipe operator, CONCAT_WS(), and FORMAT(), etc, for this purpose.

In this PostgreSQL tutorial, you have learned how to combine multiple strings into a single value using the different functions of PostgreSQL.

You may also read:

• PostgreSQL Like Operator with Multiple Values
• Postgresql Sum – How to use
• PostgreSQL ADD COLUMN + 17 Examples