PostgreSQL CONCAT: Concatenating Strings

Summary: in this tutorial, you will learn how to use the PostgreSQL CONCAT() function, CONCAT_WS() function and concatenation operator (||) to concatenate multiple strings into a single string.

PostgreSQL concatenation operator || #

To concatenate two strings into a single string, you use the concatenation operator ||

s1 || s2Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

For example, the following SELECT statement retrieves the product name and brand from the inventories table and concatenates them into a single string:

SELECT
  brand || name AS product
FROM
  inventories;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

         product
-------------------------
 AppleiPhone 14 Pro
 SamsungGalaxy S23 Ultra
 GooglePixel 7 Pro
 SonyXperia 1 IVCode language: plaintext (plaintext)

To make the information readable, we can separate the strings using a space like this:

SELECT
  brand || ' ' || name AS product
FROM
  inventories;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

         product
--------------------------
 Apple iPhone 14 Pro
 Samsung Galaxy S23 Ultra
 Google Pixel 7 Pro
 Sony Xperia 1 IVCode language: plaintext (plaintext)

CONCAT function #

Besides the concatenation operator, PostgreSQL offers the CONCAT() function that concatenates multiple strings into a string:

CONCAT(s1, s2, ...)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

For example, you can concatenate the strings from the brand column, a space, and strings in the name column into a single string:

SELECT
  CONCAT(brand, ' ', name) AS product
FROM
  inventories;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

         product
--------------------------
 Apple iPhone 14 Pro
 Samsung Galaxy S23 Ultra
 Google Pixel 7 Pro
 Sony Xperia 1 IVCode language: plaintext (plaintext)

CONCAT_WS: Concatenating strings with a separator #

To concatenate strings with a separator, you use the CONCAT_WS() function with the following syntax:

CONCAT_WS(separator, s1, s2, ..)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Note that WS stands for With Separator.

For example, the following statement uses the CONCAT_WS() function to concatenate the brand and name with the space as a separator:

SELECT
  CONCAT_WS(' ', brand, name) AS product
FROM
  inventories;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

         product
--------------------------
 Apple iPhone 14 Pro
 Samsung Galaxy S23 Ultra
 Google Pixel 7 Pro
 Sony Xperia 1 IVCode language: plaintext (plaintext)

Summary #

• Use the concatenation operator || to concatenate two strings into a string string.
• Use the CONCAT() function to concatenate two or more strings into a single string.
• Use the CONCAT_WS() function to concatenate two or more strings into a single string separated by a separator.

Quiz #