PostgreSQL CONCAT_WS Function

Summary: in this tutorial, you’ll learn how to use the PostgreSQL CONCAT_WS() function to concatenate two or more strings into a string, separated by a separator.

Introduction to the PostgreSQL CONCAT_WS function #

In PostgreSQL, the CONCAT_WS() function allows you to concatenate multiple strings into a string with separators.

In the function name CONCAT_WS , the CONCAT stands for concatenating, and WS stands for separator.

Here’s the syntax of the CONCAT_WS() function:

CONCAT_WS (separator, s1, s2, ...)

In this syntax:

• separator: This is one or more characters used as separators between input strings.
• s1, s2, … : These are strings you want to concatenate.

The CONCAT_WS() function returns a new string by concatenating all input strings with separators. It returns NULL if the separator is NULL.

The CONCAT_WS() function skips any NULL input string, avoiding unwanted gaps in the result string.

Basic PostgreSQL CONCAT_WS function examples #

The following statement uses the CONCAT_WS() function to concatenate first name and last name separated by a space:

SELECT
  CONCAT_WS(' ', 'Anthony', 'Pham') name;Code language: JavaScript (javascript)

Try it

Output:

     name
--------------
 Anthony Pham

The CONCAT_WS() function ignores NULL as shown in the following example:

SELECT
  CONCAT_WS(' ', 'Anthony', NULL, 'Pham') name;Code language: PHP (php)

Try it

Output:

     name
--------------
 Anthony Pham

Concatenating strings in the database #

The following query uses the CONCAT_WS() function to concatenate the warehouse name and address from the warehouses table:

SELECT
  CONCAT_WS(' - ', warehouse_name, address) warehouse
FROM
  warehouses
ORDER BY
  warehouse_name;Code language: JavaScript (javascript)

Try it

Output:

                                 warehouse
---------------------------------------------------------------------------
 Los Angeles Warehouse - 1919 Vineburn Avenue, Los Angeles, CA 90032
 San Francisco Warehouse - 233 E Harris Ave, South San Francisco, CA 94080
 San Jose Warehouse - 205 E Alma Ave, San Jose, CA 95112

Generating CSV data #

The following example uses the CONCAT_WS() function to generate CSV data from the product id, name, and price in the products table:

SELECT
  CONCAT_WS(',', product_id, QUOTE_IDENT(product_name), price) csv_data
FROM
  products;Code language: JavaScript (javascript)

Try it

Output:

                csv_data
-----------------------------------------
 1,"Samsung Galaxy S24",999.99
 2,"Apple iPhone 15",1099.99
 3,"Huawei Mate 60",899.99
 4,"Xiaomi Mi 14",799.99
 5,"Sony Xperia 1 VI",949.99
...Code language: JavaScript (javascript)

How the query works:

• First, use the CONCAT_WS() function to concatenate values from the product_id, product_name, and price columns with commas as separators.
• Second, surround the text fields such as product_name with double quotes using the QUOTE_IDENT() function.

Summary #

• Use the CONCAT_WS() function to concatenate strings into a string with separators.