How to Concatenate Strings in PostgreSQL

Last updated on July 17th, 2024 at 06:23 am

String concatenation is a common requirement among web developers and database programmers. They may need to combine two or more strings in SQL query result without modifying actual data. PostgreSQL allows you to easily join two or more columns, strings and int. It offers 3 ways to concatenate strings – using ‘||’ operator, using concat() and using concat_ws() function. In this article, we will look at how to concatenate strings in PostgreSQL using each of these approaches. You can use it to combine columns in PostgreSQL, string and variable, or string and int. We will also learn what to do if one of the strings is null.

How to Concatenate Strings in PostgreSQL

There are three ways to concatenate strings in PostgreSQL – using || operator, using concat and using concat_ws() function. Here is the syntax of ‘||’ operator.

str1 || str2 || str3 || ...

Here is the syntax of concat() function.

concat(str1, str2, str3, ...)

Here is the syntax of concat_ws() function.

concat_ws(separator, str1, str2, str3, ...)

In each of the above solutions, you can use string literals or column names. They can also be used with other data types such as int, floats, etc. but they will be converted into string before concatenation.

1. Concatenate String Literals with || Operator

You can easily combine two or more string literals using ‘||’ operator as shown below.

postgres=# SELECT 'Good' || ' ' || 'Morning' AS greeting;

 greeting
-----------
 Good Morning

2. Join String with Int Using || Operator

You can also use ‘||’ operator to combine string with int data types. In this case, the int data is first converted into string, before concatenation.

postgres=# SELECT 'Good' || ' ' || 1 AS greeting;

 greeting
-----------
 Good 1

3. Concatenate String with Null Using || Operator

Please note, || operator cannot be used to combine string or int with null values. The result will always be null.

postgres=# SELECT 'Good' || NULL || 'Morning' AS greeting;

 greeting
-----------

4. Concatenate Two Columns Using || operator

PostgreSQL allows you to directly concatenate strings, columns and int values using || operator.

Let us say you have a table employees(id, first_name, last_name)

postgres=# create table employees(id int, 
              first_name varchar(10), 
              last_name varchar(10));

postgres=# insert into employees(id,first_name,last_name)
           values(1,'Jim','Doe'),
                 (2,'Tim','Don'),
                 (3,'Jane','Doe');

postgres=# select * from employees;
 id | first_name | last_name
----+------------+-----------
  1 | Jim        | Doe
  2 | Tim        | Don
  3 | Jane       | Doe

Here is the SQL query to concatenate columns first_name and last_name using || operator.

postgres-# select first_name || ' ' || last_name as full_name
           from employees;
 full_name
-----------
 Jim Doe
 Tim Don
 Jane Doe

You can even concatenate string with int using || operator.

select first_name || 123 || last_name as full_name
postgres-#            from employees;
 full_name
------------
 Jim123Doe
 Tim123Don
 Jane123Doe

As you can see || operator automatically converts int to string while concatenating.

Here also, || operator cannot concatenate null values. If there is null in any of the column or string, then the result is a null value.

postgres-#  select first_name || null || last_name as full_name
            from employees;
 full_name
-----------

(3 rows)

For that purpose, we need to use concat function, which is described below.

5. Combine String Literals using concat function

Starting PostgreSQL 9.1, Concat function also allows you to concatenate strings, columns and int. It accepts comma-separated parameters. These parameters can be strings, columns, int, null, etc.

The key difference between || operator and concat function is that, if any of the values in concat function is null, it is automatically converted into an empty string during concatenation.

Here is an example to combine two or more string literals using concat() function.

postgres=# SELECT concat('Good',' ','Morning') AS greeting;

 greeting
-----------
 Good Morning

6. Join String with Int Using concat function

You can also combine string with other data types such as int, float, dates, etc. using concat() function. All non-string data types will be converted into strings before joining them.

postgres=# SELECT concat('Good', ' ', 123) AS greeting;

 greeting
-----------
 Good 123

7. Combine String with Null using Concat Function

Unlike || operator, if any of the strings are null, concat() function will convert it into empty string, before concatenation. This is an advantage of using concat() compared to || operator.

postgres=# SELECT concat('Good', NULL, 'Morning') AS greeting;

 greeting
-----------
 GoodMorning

8. Concatenate Columns using concat function

Here is an example to concatenate first_name and last_name column above using concat function.

postgres-# select concat(first_name,' ',last_name) as full_name 
           from employees;
 full_name
-----------
 Jim Doe
 Tim Don
 Jane Doe

Here is an example to concatenate first_name and last_name columns with null value

postgres=# select concat(first_name,null,last_name) as full_name
            from employees;
 full_name
-----------
 JimDoe
 TimDon
 JaneDoe

As you can see, concat function has converted null values into empty strings.

9. Using Concat_ws() function

So far, in all the above examples, the result is direct concatenation of strings as they are. Sometimes, you may need to insert a separator between strings in the final result of concatenation. For this purpose, you can use concat_ws() function. It basically concatenates strings with a separator. Here is its syntax.

CONCAT_WS(separator, str1, str2, ...);

Here is a simple example to concatenate strings with comma separator.

postgres=# SELECT concat_ws(',','John', 41, 'Male') AS details;

 details
-----------
 John,41,Male

You can also use it to combine columns.

postgres=# SELECT concat_ws(',',name, age, gender) from employees AS details;

 details
-----------
 John,41,Male

As you can see, it is very useful to quickly concatenate data using a separator.

Conclusion

In this article, we have learnt several simple ways to easily concatenate strings in PostgreSQL using ‘||’ operator as well as concat() function. While using ‘||’ operator, it is important to remember that if any of the strings is null, then the result will be null. We have also learnt how to use concat_ws() function which is a variation of concat() function. String concatenation is a common requirement in almost every website and app, especially where you need to display user details such as name, address, etc. Depending on your requirement, you can use any of the solutions described in this article.

Need a reporting tool for PostgreSQL? Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!

Also read :
How to Compare Arrays in PostgreSQL
How to Change User Password in PostgreSQL
How to Update Multiple Columns in PostgreSQL