PostgreSQL- CONCAT Function
Last Updated :
11 Nov, 2024
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.
Similar Reads
SQL Interview Questions Are you preparing for a SQL interview? SQL is a standard database language used for accessing and manipulating data in databases. It stands for Structured Query Language and was developed by IBM in the 1970's, SQL allows us to create, read, update, and delete data with simple yet effective commands.
15+ min read
SQL Tutorial SQL is a Structured query language used to access and manipulate data in databases. SQL stands for Structured Query Language. We can create, update, delete, and retrieve data in databases like MySQL, Oracle, PostgreSQL, etc. Overall, SQL is a query language that communicates with databases.In this S
11 min read
SQL Commands | DDL, DQL, DML, DCL and TCL Commands SQL commands are crucial for managing databases effectively. These commands are divided into categories such as Data Definition Language (DDL), Data Manipulation Language (DML), Data Control Language (DCL), Data Query Language (DQL), and Transaction Control Language (TCL). In this article, we will e
7 min read
SQL Joins (Inner, Left, Right and Full Join) SQL joins are fundamental tools for combining data from multiple tables in relational databases. Joins allow efficient data retrieval, which is essential for generating meaningful observations and solving complex business queries. Understanding SQL join types, such as INNER JOIN, LEFT JOIN, RIGHT JO
6 min read
Normal Forms in DBMS In the world of database management, Normal Forms are important for ensuring that data is structured logically, reducing redundancy, and maintaining data integrity. When working with databases, especially relational databases, it is critical to follow normalization techniques that help to eliminate
7 min read
ACID Properties in DBMS In the world of DBMS, transactions are fundamental operations that allow us to modify and retrieve data. However, to ensure the integrity of a database, it is important that these transactions are executed in a way that maintains consistency, correctness, and reliability. This is where the ACID prop
8 min read
Introduction of DBMS (Database Management System) A Database Management System (DBMS) is a software solution designed to efficiently manage, organize, and retrieve data in a structured manner. It serves as a critical component in modern computing, enabling organizations to store, manipulate, and secure their data effectively. From small application
8 min read
SQL Query Interview Questions SQL or Structured Query Language, is the standard language for managing and manipulating relational databases such as MySQL, Oracle, and PostgreSQL. It serves as a powerful tool for efficiently handling data whether retrieving specific data points, performing complex analysis, or modifying database
15 min read
CTE in SQL In SQL, a Common Table Expression (CTE) is an essential tool for simplifying complex queries and making them more readable. By defining temporary result sets that can be referenced multiple times, a CTE in SQL allows developers to break down complicated logic into manageable parts. CTEs help with hi
6 min read
Window Functions in SQL SQL window functions are essential for advanced data analysis and database management. They enable calculations across a specific set of rows, known as a "window," while retaining the individual rows in the dataset. Unlike traditional aggregate functions that summarize data for the entire group, win
7 min read