In PostgreSQL, the CONCAT_WS() function concatenates two or more strings, placing a separator between each one. The separator is specified by the first argument.

Syntax

The syntax goes like this:

concat_ws(sep text, str "any" [, str "any" [, ...] ])

Where sep text is the separator to use.

Example

Here’s an example to demonstrate:

SELECT CONCAT_WS(',', 'Red', 'Green');

Result:

Red,Green

In this case, I concatenated the strings with a comma as the separator.

Here’s an example that uses a space as the separator:

SELECT CONCAT_WS(' ', 'Squid', 'Game');

Result:

Squid Game

Concatenating Numbers

PostgreSQL concatenates the text representation of its arguments, so we can concatenate numbers without having to explicitly convert them to a string:

SELECT CONCAT_WS(',', 1, 2, 3);

Result:

1,2,3

Although, be careful if there are leading zeros:

SELECT CONCAT_WS(',', 001, 002, 003);

Result:

1,2,3

Null Arguments

Null arguments are ignored:

SELECT CONCAT_WS(' ', 'Player', NULL, 456);

Result:

Player 456

No Arguments

Calling CONCAT_WS() without passing any arguments results in an error:

SELECT CONCAT_WS();

Result:

ERROR:  function concat_ws() does not exist
LINE 1: SELECT CONCAT_WS();
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Passing an Array

It’s possible to pass an array to the CONCAT_WS() function so that each array element becomes treated as a separate argument to be concatenated. To do this, prefix the array with the VARIADIC keyword:

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

Result:

Cat-Dog-Horse

We can see that each element in the array has become a separate string to be concatenated, and the separator works just as it did in the previous examples.