Summary : in this tutorial, we will show you how to use the PostgreSQL CONCAT and CONCAT_WS functions to concatenate two or more strings into one.
Introduction to PostgreSQL CONCAT function
To concatenate two or more strings into one, you use the string concatenation operator || as the following example:
SELECT
'Concatenation' || ' ' || 'Operator' AS result_string;Code language: SQL (Structured Query Language) (sql)
The following statement concatenates a string with a
NULL
value:
SELECT
'Concat with ' || NULL AS result_string;Code language: SQL (Structured Query Language) (sql)
It returns a
NULL
value.
Since version 9.1, PostgreSQL introduced a built-in string function named
CONCAT
to concatenate two or more strings into one.
The following illustrates the syntax of the
CONCAT
function:
CONCAT(str_1, str_2, ...)Code language: SQL (Structured Query Language) (sql)
The
CONCAT
function accepts a list of arguments. The argument needs to be convertible to a string. A string in this context means any of the following
data types
: char, varchar, or text.
The
CONCAT
function is variadic. It means that the
CONCAT
function accepts an
array
as the argument. In this case, you need to mark the array with the
VARIADIC
keyword. The
CONCAT
function treats each array element as an argument.
Unlike the concatenation operator ||, the
CONCAT
function ignores
NULL
arguments. We will see it in detail in the following section.
PostgreSQL CONCAT function examples
The following
SELECT statement
uses the
CONCAT
function to concatenate two strings into one:
SELECT
CONCAT ('CONCAT',' ', 'function');Code language: SQL (Structured Query Language) (sql)
The following statement concatenates values in the
first_name
and
last_name
columns of the
customer
table in the
sample database
.
SELECT
CONCAT (first_name, ' ', last_name) AS "Full name"
customer;Code language: SQL (Structured Query Language) (sql)
In the following example, we use
CONCAT
function to concatenate a string with a
NULL
value.
SELECT
CONCAT('Concat with ',NULL) AS result_string;Code language: SQL (Structured Query Language) (sql)
As you see, unlike the string concatenation operator ||, the
CONCAT
function ignores the
NULL
arguments.
The following statement concatenates strings with a number returned from the LENGTH function .
SELECT
first_name,
concat (
'Your first name has ',
LENGTH (first_name),
' characters'