Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://www.geeksforgeeks.org/postgresql-concat-function/ below:

PostgreSQL- CONCAT Function - GeeksforGeeks

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.

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.

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.

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'.

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.

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.

• 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.

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.

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4