concat_ws()

A function for concatenating values with a separator

concat_ws() is a system function for concatenating arbitrary values into a text string joined by a separator (delimiter).

concat_ws() was added in PostgreSQL 9.1.

Usage

concat_ws ( sep text , val1 any [, val2 any [, ...] ] ) → text

The first argument is the separator string to use, which can be any non-NULL value.

To aggregate string values from colums in a query, use string_agg().

Basic usage example for concat_ws():

postgres=# SELECT concat_ws('|', 'foo', 'bar');
 concat_ws 
-----------
 foo|bar
(1 row)

NULL values are ignored:

postgres=# SELECT concat_ws('|', 'foo', NULL, 'bar');
 concat_ws 
-----------
 foo|bar
(1 row)

An empty string will be returned if all provided values are NULL:

postgres=# SELECT concat_ws('|', NULL, NULL) IS NULL;
 ?column? 
----------
 f
(1 row)

However, a NULL separator results in a NULL result:

postgres=# SELECT concat_ws(NULL, 'foo', 'bar') IS NULL;
 ?column? 
----------
 t
(1 row)

Providing an empty string as the separator is functionally the same as using concat():

postgres=# SELECT concat_ws('', 'foo', 'bar'), concat('foo', 'bar');
 concat_ws | concat 
-----------+--------
 foobar    | foobar
(1 row)