PostgreSQL CONCAT Function (original) (raw)

Last Updated : 15 Jul, 2025

The **PostgreSQL CONCAT function allows us to combine multiple strings or column values into a single output, making it a flexible tool for **data manipulation. This function is essential for **string concatenation tasks, whether we’re working with static text, columns from a **database table, or dynamic **SQL queries.

In PostgreSQL, we have the **CONCAT function and the **concatenation operator ( || ), each with unique advantages. In this guide, we will explain how to use the **PostgreSQL CONCAT function, understand its **syntax, and cover **practical examples using tables in a sample **database for clarity.

What is the PostgreSQL CONCAT Function?

The **PostgreSQL CONCAT function concatenates (combines) two or more strings into a single string, handling **NULL values gracefully by ignoring them. Introduced in PostgreSQL 9.1, CONCAT provides flexibility in string concatenation by accepting multiple arguments of string types like **CHAR, **VARCHAR, and **TEXT.

**Syntax

CONCAT(string_1, string_2, ...)

**Key Terms

**String Convertible Arguments: The CONCAT function accepts a variable number of arguments, each convertible to a string (**CHAR, **VARCHAR, or **TEXT).
**Variadic Functionality: Since CONCAT is variadic, it can take a list of strings as arguments or accept an array using the VARIADIC keyword.
**NULL Handling: Unlike the || operator, **CONCAT ignores NULL values in the concatenation process, which prevents NULL results in the final string.

PostgreSQL CONCAT Function Examples

Let us take a look at some of the examples of the **CONCAT Function in **PostgreSQL to better understand the concept. We will use a **dvdrental sample database to showcase **real-world applications.

**Example 1: Concatenating Multiple Strings

The **CONCAT function efficiently combines the strings in the order provided, without any delimiters, making it ideal for straightforward string **concatenation tasks. The below statement uses the CONCAT function to **concatenate three strings into one

**Query:

SELECT CONCAT ('Geeks', 'for', 'geeks');

**Output

PostgreSQL CONCAT Function Example

**Explanation:

The result of this query will be a single string: ' GeeksforGeeks'.

**Example 2: Concatenating Columns with Static Text

The following statement concatenates values in the '**first_name' and '**last_name' columns of the actor table in the sample database, ie, dvdrental.

**Query:

SELECT CONCAT (first_name, ' ', last_name) AS "Full name"
FROM actor;

**Output

PostgreSQL CONCAT Function Example

**Explanation:

Here, the CONCAT function merges each actor's first and last names with a space in between, listing them alphabetically.

Example 3: Using CONCAT with NULL Values

This example demonstrates how NULL values are handled. We’ll use a **contacts table and concatenate the columns **name, **email, and **phone.

**Query:

CREATE TABLE contacts (
id SERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
email VARCHAR(255),
phone VARCHAR(15)
);

INSERT INTO contacts (name, email, phone)
VALUES
('John Doe', 'john@gmail.com', '123-456-7890'),
('Jane Smith', 'jane.smith@gmail.com', NULL);

SELECT CONCAT(name, ' (', email, ') ', phone) AS contact_info
FROM contacts;

**Output

contact_info

John Doe (john@gmail.com) 123-456-7890
Jane Smith (jane.smith@gmail.com)

**Explanation:

NULL values (e.g., missing phone numbers) are ignored, making the **CONCAT function useful for avoiding unexpected **NULL results in concatenated strings.

Important Points About PostgreSQL CONCAT Function

The CONCAT function is variadic, meaning it can take a variable number of arguments, including arrays.
Unlike the concatenation operator (||), the CONCAT function ignores **NULL**arguments, which prevents unexpected NULL results in your concatenated strings.
The CONCAT function can be easily combined with other PostgreSQL functions like**UPPER(), **LOWER(), **TRIM(), and more to perform complex string manipulations.
The CONCAT function is supported in PostgreSQL 9.1 and later.

Conclusion

In conclusion, the **CONCAT() function in PostgreSQL provides a flexible solution for **string concatenation, allowing us to efficiently combine multiple text values or columns into a single output. By understanding the **syntax of the CONCAT() function and its usage with examples, we can handle various data formatting tasks with ease. Whether we use **CONCAT() or the concatenation operator (||), **PostgreSQL offers flexible options to meet our requirements.