PostgreSQL CONCAT() Function

Introduction to CONCAT in PostgreSQL

The CONCAT function in PostgreSQL is used to concatenate multiple strings together, creating a single string as the result. It takes two or more arguments, each being a string, and returns a new string that is the concatenation of all the input strings.

The syntax of the CONCAT function is as follows:

where string1, string2, and so on are the strings you want to concatenate. You can provide as many strings as needed, and they will be combined in the order you specify.

For example:

Output:

The CONCAT function is particularly useful when you need to combine columns or literals to create more informative outputs or when constructing dynamic queries.

CONCAT in PostgreSQL Examples

Example of How to Use CONCAT() Function in PostgreSQL

Let's start with a simple example of using the CONCAT function in PostgreSQL:

Suppose we have a table named employees with columns first_name and last_name, and we want to display the full names of the employees.

Now, let's use the CONCAT function to retrieve the full names:

Output:

The CONCAT function efficiently combines the first_name and last_name columns, separated by a space, to give us the desired output.

How to Concatenate Multiple Strings Using CONCAT() Function

You can also concatenate more than two strings using the CONCAT function. Let's say we want to create a list of employees with their job titles:

Output:

In this example, we concatenated the strings "Employee: ", first_name, " ", last_name, ", Job Title: ", and the job_title column, resulting in the employee_info column.

How to Use the CONCAT() Function With Built-in Functions

The CONCAT function can also be used in combination with other built-in functions to create more complex concatenated strings. For instance, let's say we want to display the full names of employees in uppercase letters:

Output:

Here, we used the UPPER function to convert the first_name and last_name columns to uppercase before concatenating them.

Example of PostgreSQL CONCAT() Function with NULL

When using the PostgreSQL CONCAT function, if any of the input strings is NULL, the result will still be non-NULL. The CONCAT function treats NULL as an empty string during concatenation. As a result, the final result will not be NULL, but rather the concatenation of the non-NULL strings.

Let's run the query to confirm the output:

Output:

As you can see, the output is 'HelloWorld', which is the concatenation of the non-NULL strings 'Hello' and 'World'. The NULL value in the middle did not result in the entire concatenation being NULL.

Example of PostgreSQL CONCAT() Function using Column

You can also concatenate strings with literal values. For instance, let's say we want to add a prefix to the job titles of employees:

Output:

In this example, we concatenated the string "Job Title: " with the job_title column.

Example of Concatenating a Prefix and Suffix

Suppose you have a table named products with columns product_name and price, and you want to display a list of products with a prefix "Product: " and a suffix " - $".

Now, let's use the CONCAT function to achieve this:

Output:

In this example, we concatenated the prefix "Product: ", product_name, the separator " - $", and the price column to create a formatted product information.

Example of Concatenating Strings with Conditional Logic

You can use the CONCAT function along with conditional logic to concatenate strings based on specific conditions. Let's say you have a table named students with columns first_name, last_name, and gender, and you want to display a list of students with their titles ("Mr." or "Ms.") based on their gender.

Now, let's use the CONCAT function along with a CASE statement to achieve this:

Output:

In this example, we used a CASE statement to determine the appropriate title for each student based on their gender and then concatenated it with their first_name and last_name.

PostgreSQL CONCAT_WS Function

In addition to the CONCAT function, PostgreSQL also provides the CONCAT_WS function, which is specifically designed for concatenating strings with a separator. The "WS" stands for "With Separator". The function takes a separator as its first argument, followed by the strings to concatenate.

The syntax of the CONCAT_WS function is as follows:

where separator is the string that will be used to separate the other strings. The string1, string2, and so on are the strings you want to concatenate.

Let's look at an example of how to use CONCAT_WS:

Output:

In this example, the strings "apple", "banana", and "orange" were concatenated using the separator ", " to create a list of fruits.