Summary
: in this tutorial, you will learn how to use the PostgreSQL
CONCAT()
function to concatenate two or more strings into one.
Neon Postgres
– Fully Automated, Generous Free Tier, Instant DBs, Autoscaling, Git-like Branching -
Start Free
Sponsored
Introduction to PostgreSQL CONCAT() function
To concatenate two or more strings into a single string, you can use the string concatenation operator || as shown in the following example:
SELECT
'John' || ' ' || 'Doe' AS full_name;Code language: SQL (Structured Query Language) (sql)Output:
full_name
-----------
John Doe
(1 row)
The following statement uses the concatenation operator (
||
) to concatenate a string with
NULL
:
SELECT
'John' || NULL result;Code language: SQL (Structured Query Language) (sql)
It returns
NULL
.
result
--------
(1 row)
Code language: JavaScript (javascript)
Since version 9.1, PostgreSQL has introduced a built-in string function called
CONCAT()
to concatenate two or more strings into one.
Here’s the basic syntax of the
CONCAT()
function:
CONCAT(string1, string2, ...)Code language: SQL (Structured Query Language) (sql)
The
CONCAT
function accepts a list of input strings, which can be any string type including
CHAR
,
VARCHAR
, and
TEXT
.
The
CONCAT()
function returns a new string that results from concatenating the input strings.
Unlike the concatenation operator
||
, the
CONCAT
function ignores
NULL
arguments.
To concatenate two or more strings into one using a specified separator, you can use the CONCAT_WS() function.
PostgreSQL CONCAT() function examples
Let’s take some examples of using the PostgreSQL
CONCAT()
function.
1) Basic PostgreSQL CONCAT() function example
The following example uses the
CONCAT()
function to concatenate three literal strings into one:
SELECT
CONCAT ('John', ' ', 'Doe') full_name;Code language: SQL (Structured Query Language) (sql)Output:
full_name
-----------
John Doe
(1 row)2) Using the CONCAT() function with table data example
We’ll use the
customer
table from the
sample database
:
The following statement uses the
CONCAT()
function to concatenate values in the
first_name
, a space, and values in the
last_name
columns of the
customer
table into a single string:
SELECT
CONCAT (first_name, ' ', last_name) AS full_name
customer
ORDER BY
full_name;Code language: SQL (Structured Query Language) (sql)Output:
full_name
-----------------------
Aaron Selby
Adam Gooch
Adrian Clary
Agnes Bishop
Alan Kahn
...3) Using the CONCAT() function with NULL
First,
create a table
called
contacts
and
insert some rows
into it:
CREATE TABLE contacts (
id SERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL,
phone VARCHAR(15)
INSERT INTO contacts (name, email, phone)
VALUES
('John Doe', '[email protected]', '123-456-7890'),
('Jane Smith', '[email protected]', NULL),
('Bob Johnson', '[email protected]', '555-1234'),
('Alice Brown', '[email protected]', NULL),
('Charlie Davis', '[email protected]', '987-654-3210')
RETURNING *;Code language: SQL (Structured Query Language) (sql)Output:
id | name | email | phone
----+---------------+---------------------+--------------
1 | John Doe | [email protected] | 123-456-7890
2 | Jane Smith | [email protected] | null
3 | Bob Johnson | [email protected] | 555-1234
4 | Alice Brown | [email protected] | null
5 | Charlie Davis | [email protected] | 987-654-3210
(5 rows)
INSERT 0 5Code language: JavaScript (javascript)
Second, use the
CONCAT()
function to concatenate the values in the
name
,
email
, and
phone
columns of the
contacts
table:
SELECT
CONCAT(name, ' ', '(', email, ')', ' ', phone) contact
contacts;Code language: SQL (Structured Query Language) (sql)Output:
contact
--------------------------------------------------