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;

Output:

full_name----------- John Doe(1 row)

The following statement uses the concatenation operator (||) to concatenate a string withNULL:

SELECT   'John' || NULL result;

It returnsNULL.

result-------- null(1 row)

Since version 9.1, PostgreSQL has introduced a built-in string function calledCONCAT() to concatenate two or more strings into one.

Here's the basic syntax of theCONCAT() function:

CONCAT(string1, string2, ...)

TheCONCAT function accepts a list of input strings, which can be any string type includingCHAR,VARCHAR, andTEXT.

TheCONCAT() function returns a new string that results from concatenating the input strings.

Unlike the concatenation operator||, theCONCAT function ignoresNULL arguments.

To concatenate two or more strings into one using a specified separator, you can use theCONCAT_WS() function.

PostgreSQL CONCAT() function examples

Let's take some examples of using the PostgreSQLCONCAT() function.

1) Basic PostgreSQL CONCAT() function example

The following example uses theCONCAT() function to concatenate three literal strings into one:

SELECT  CONCAT ('John',' ','Doe') full_name;

Output:

full_name----------- John Doe(1 row)

2) Using the CONCAT() function with table data example

We'll use thecustomer table from thesample database:

The following statement uses theCONCAT() function to concatenate values in thefirst_name, a space, and values in thelast_name columns of thecustomer table into a single string:

SELECT  CONCAT (first_name,' ', last_name) AS full_nameFROM  customerORDER BY  full_name;

Output:

full_name----------------------- Aaron Selby Adam Gooch Adrian Clary Agnes Bishop Alan Kahn...

3) Using the CONCAT() function with NULL

First,create a table calledcontacts andinsert 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', 'john.doe@example.com', '123-456-7890'),    ('Jane Smith', 'jane.smith@example.com', NULL),    ('Bob Johnson', 'bob.johnson@example.com', '555-1234'),    ('Alice Brown', 'alice.brown@example.com', NULL),    ('Charlie Davis', 'charlie.davis@example.com', '987-654-3210')RETURNING *;

Output:

id |     name      |        email        |    phone----+---------------+---------------------+--------------  1 | John Doe      | john.doe@example.com      | 123-456-7890  2 | Jane Smith    | jane.smith@example.com    | null  3 | Bob Johnson   | bob.johnson@example.com     | 555-1234  4 | Alice Brown   | alice.brown@example.com   | null  5 | Charlie Davis | charlie.davis@example.com | 987-654-3210(5 rows)INSERT 0 5

Second, use theCONCAT() function to concatenate the values in thename,email, andphone columns of thecontacts table:

SELECT  CONCAT(name, ' ', '(', email, ')', ' ', phone) contactFROM  contacts;

Output:

contact-------------------------------------------------- John Doe (john.doe@example.com) 123-456-7890 Jane Smith (jane.smith@example.com) Bob Johnson (bob.johnson@example.com) 555-1234 Alice Brown (alice.brown@example.com) Charlie Davis (charlie.davis@example.com) 987-654-3210(5 rows)

The output indicates that theCONCAT() function ignoresNULL.

Summary

• Use the PostgreSQLCONCAT() function to concatenate two or more strings into one.

Last updated onJanuary 28, 2024

On this page

• Introduction to PostgreSQL CONCAT() function
• PostgreSQL CONCAT() function examples
• Summary