When working with databases (and software in general), string concatenation is the operation of joining character strings end-to-end. For example if we have two words, we can combine them into one.

PostgreSQL provides us with multiple ways to concatenate strings. Below are two functions and one operator that we can use to concatenate strings in PostgreSQL.

The || Operator

The || operator can be used to concatenate two strings:

SELECT 'Bird' || 'Dog';

Result:

BirdDog

If we want to insert a separator, we can include an extra concatenation:

SELECT 'Bird' || ', ' || 'Dog';

Result:

Bird, Dog

We can use the || operator on text values and non-array values. Therefore, we can do the following:

SELECT 'Bird' || 101;

Result:

Bird101

But if we try to concatenate an array, we get an error:

SELECT 'Bird' || ARRAY[1,2,3];

Result:

ERROR:  malformed array literal: "Bird"
LINE 1: SELECT 'Bird' || ARRAY[1,2,3];
               ^
DETAIL:  Array value must start with "{" or dimension information.

However, we can use the array_to_string() function to convert the array’s elements to text:

SELECT 'Bird' || array_to_string(ARRAY[1,2,3], ',');

Result:

Bird1,2,3

The CONCAT() Function

We can alternatively use the concat() function to concatenate the strings:

SELECT concat('Bird', 'Dog');

Result:

BirdDog

The function accepts a variable number of arguments, so we can include a separator by adding another concatenation:

SELECT concat('Bird', ', ', 'Dog');

Result:

Bird, Dog

And we can combine strings with numbers (although the function always returns a text value, so numbers are converted to text):

SELECT concat('Bird', 101);

Result:

Bird101

One difference between concat() and the || operator is that concat() allows us to include arrays in the concatenation:

SELECT concat('Bird', ARRAY[1,2,3]);

Result:

Bird{1,2,3}

In this case the array is concatenated, along with its curly braces.

We can also use array_to_string() to convert the array elements to a string:

SELECT concat('Bird', array_to_string(ARRAY[1,2,3], ','));

Result:

Bird1,2,3

The CONCAT_WS() Function

If we need to use a separator in our concatenated strings, we’re usually better off using the concat_ws() function. This allows us to specify a separator to use, which means we don’t have to add that separator as a separate string to concatenate. This can be especially handy if we have many strings to concatenate:

SELECT concat_ws(', ', 'Bird', 'Dog', 'Cat', 'Ant');

Result:

Bird, Dog, Cat, Ant

It works with arrays:

SELECT concat_ws('-', 'Bird', ARRAY[1,2,3]);

Result:

Bird-{1,2,3}

And we can use array_to_string() to convert the array elements to a string:

SELECT concat_ws('-', 'Bird', array_to_string(ARRAY[1,2,3], ','));

Result:

Bird-1,2,3

In this case I used a different separator for the array_to_string() function than I used for the concat_ws() function. Here it is using the same separator:

SELECT concat_ws('-', 'Bird', array_to_string(ARRAY[1,2,3], '-'));

Result:

Bird-1-2-3