How to Concat in Postgres

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

Additionally, I will show the function which combines multiple strings into a single string with a separator.

How to Concat in Postgres

The full name of the concat is concatenate, the concatenate means 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 concat the string or text in PostgreSQL and for that, PostgreSQL provides different functions.

From a database scenario, concatenation can be helpful in situations where you want to combine the first or last name of the employee which exists in different columns of the table.

Also, you can represent the information in a more meaningful way by concatenating the different values into a single value.

Let’s see some example that uses different functions for the concatenation of the string or text values.

How to Concat in Postgres using Pipe Operator

The pipe || operator of Postgres combines multiple or more than one string/text 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 table ‘orders’ with columns first_name, last_name, and order_date.

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

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

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 value of columns first_name and last_name are concatenated using the pipe || operator as you can see in the above query.

How to Concat in Postgres using CONCAT() function

PostgreSQL has a concat() function which takes multiple strings and combines them into single-string values. In general, you pass the list of elements to the concat() function and this element should be a convertible string.

You can provide different types of string data such as varchar, char, and text. The concat() function is called the VARIADIC function which means it also accepts the array data. 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(): It is the concat() function itself which accepts the multiple string data.
• stringValue1, stringValue2, stringValue3, …..: These are the multiple string values that are combined together 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;

If you look at the above query, the concat() function contains three values, the first is the column ‘name’, the second is the string ‘ lives in ‘, and the third is the column ‘country’.

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

How to Concat in Postgres using CONCAT_WS() function

The concat_ws() function of PostgreSQL also combine the multiple strings into a single value but with a separator. It accepts multiple strings with a separator means how you want to separate the combined string, the separator can be a comma (,), colon (:) and etc.

The syntax is given below.

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

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

Let’s take the same example that you have done in the above topics.

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

In the above query, the concat_ws() function contains four values, first is the separator hyphen (-), the second is 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.

Conclusion

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