How to Concatenate Strings in PostgreSQL

SQL Tutorial Home

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

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

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

IN THIS PAGE