how to concatenate string in postgresql

How to Concatenate Strings in PostgreSQL

PostgreSQL allows you to easily concatenate columns, strings and int. PostgreSQL offers two ways to concatenate strings. In this article, we will look at how to concatenate strings in PostgreSQL using each of these approaches. You can use it to concatenate columns in PostgreSQL, concatenate string and variable, or concatenate string and int.


How to Concatenate Strings in PostgreSQL

There are two ways to concatenate strings in PostgreSQL – using || operator and using concat function.


Concatenate String 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

Also read : How to Update Multiple Columns in PostgreSQL


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.

However, || 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.

Also read : How to Compare Arrays in PostgreSQL


Concatenate String using concat function

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 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

Also read : How to Change User Password in PostgreSQL

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.

Hopefully, this article will help you concatenate columns in PostgreSQL, concatenate string and variable, or concatenate string and int.

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

mm

About Ubiq

Ubiq is a powerful dashboard & reporting platform. Build dashboards, charts & reports for your business in minutes. Try it for free!