How to Concatenate Strings in PostgreSQL

PostgreSQL offers two ways to concatenate strings. The first uses the || operator:

select 'Join these ' || 'strings with a number ' || 23;

 result               
-------------------------------------
 Join these strings with a number 23

You can see from above that PostgreSQL took care of transforming the number to a string to attach it to the rest. Note that you also need to consciously add spaces to make the string readable. For example, in the customer table of the Sakila database, to join the first and last names of the customers you have to add a single space in the concatenation:

select first_name||' '||last_name as customer_name from customer limit 5;

 customer_name   
------------------
 MARY SMITH
 PATRICIA JOHNSON
 LINDA WILLIAMS
 BARBARA JONES
 ELIZABETH BROWN

One disadvantage of using the || operator is a null value in any of the columns being joined together will result in a null value.

select 'Null with ||' || 'will make ' || 'everything disappear' || null;

 result
----------

Using concat() will transform the nulls into empty strings when concatenating:

select concat('Concat() handles', null, ' nulls better', null);

 concat            
-------------------------------
 Concat() handles nulls better

concat()accepts multiple parameters, separated by commas.