You may be aware that PostgreSQL has a couple of functions that allow us to concatenate strings. In particular, the concat() function allows us to concatenate multiple strings into one string, and the concat_ws() function allows us to do the same, but to also specify a separator for the concatenated strings.

But did you know that we have the ability to pass an array to these functions?

The concat() and concat_ws() functions are variadic, which mean that they accept a variable number of arguments. This means that we can pass an array to these functions in a way that the function will use each array element as a separate string to be concatenated. The trick is to use the VARIADIC keyword for the array.

Example

Here’s an example that demonstrates how to concatenate array elements with the concat() function:

SELECT concat( VARIADIC ARRAY[ 'Cat', 'Dog', 'Horse' ] );

Result:

CatDogHorse

Here, I built an array using the ARRAY constructor. But I also prefixed this with the VARIADIC keyword. This had the effect of passing each array element to the concat() function as a separate string to be concatenated.

Here’s what happens when we don’t use the VARIADIC keyword:

SELECT concat( ARRAY[ 'Cat', 'Dog', 'Horse' ] );

Result:

{Cat,Dog,Horse}

The whole array is returned as a text value. The elements haven’t been concatenated at all.

Using a Separator

We can use the concat_ws() function to insert a separator between each concatenated array element:

SELECT concat_ws( '-', VARIADIC ARRAY[ 'Cat', 'Dog', 'Horse' ] );

Result:

Cat-Dog-Horse

In this case I used a hyphen character as the separator but we could use whatever we want.

Some examples:

\x
SELECT 
    concat_ws( ' ', VARIADIC ARRAY[ 'Cat', 'Dog', 'Horse' ] ) AS " ",
    concat_ws( ', ', VARIADIC ARRAY[ 'Cat', 'Dog', 'Horse' ] ) AS ", ",
    concat_ws( ' and a ', VARIADIC ARRAY[ 'Cat', 'Dog', 'Horse' ] ) AS " and a ",
    concat_ws( '>>>', VARIADIC ARRAY[ 'Cat', 'Dog', 'Horse' ] ) AS ">>>";

Result (using vertical output):

        | Cat Dog Horse
,       | Cat, Dog, Horse
 and a  | Cat and a Dog and a Horse
>>>     | Cat>>>Dog>>>Horse

As with the concat() function, omitting the VARIADIC keyword results in the whole array being returned as text:

SELECT concat_ws( '-', ARRAY[ 'Cat', 'Dog', 'Horse' ] );

Result:

{Cat,Dog,Horse}