PostgreSQL- CONCAT Function

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

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

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', '[email protected]', '123-456-7890'),
    ('Jane Smith', '[email protected]', NULL);

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

Output

contact_info
-----------------------------
John Doe ([email protected]) 123-456-7890
Jane Smith ([email protected])

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.