In PostgreSQL, CONCAT() is a built-in function that concatenates multiple strings. It accepts multiple strings as arguments and concatenates them into one string. PostgreSQL offers another built-in function named CONCAT_WS() that concatenates multiple strings and adds a separator between them.

Let’s learn the working of CONCAT() and CONCAT_WS() functions using some examples.

How to Use CONCAT() Function in PostgreSQL?

Firstly, let’s understand the basic syntax of the CONCAT() function:

CONCAT(string_1, string_2, ..., string_N);

The string can be of any type, i.e., char, text, or varchar. You can specify any number of strings in the CONCAT() function. Moreover, the CONCAT() function can accept an array as an argument. The array must be marked with the VARIADIC keyword in such a case.

Example #1: How to Concatenate Multiple Strings Using CONCAT() Function?

Execute the below query to concatenate several strings using the CONCAT() function:

SELECT CONCAT('Welcome', ' to', ' commandprompt.com');

We specified three strings in the CONCAT() function. On successfully executing the above statement, we will get a single concatenated string:

This is how the CONCAT() function works in Postgres.

Example #2: How to Concatenate Multiple Columns Data Using the CONCAT() Function?

We have a table named “article_details” that contains the following records:

SELECT * FROM article_details;

Suppose we have to combine the values of two columns: article_title and published_date. To do that, we will utilize the CONCAT() function as follows:

SELECT CONCAT(article_title, ' ', published_date)
FROM article_details;

The CONCAT() function will concatenate the values of both these columns. Values will be separated with white space:

As shown in the output, both columns have been successfully concatenated. However, the concatenated values are hard to understand. To get user-friendly output, we can specify a string in place of white space:

SELECT CONCAT(article_title, ' published on ', published_date)
FROM article_details;

Now, the concatenated strings are more understandable.

Example #3: How to Use the CONCAT() Function With Built-in Functions?

PostgreSQL offers a wide range of built-in functions that can be used with the CONCAT() function to achieve different functionalities. For example, if we want to concatenate only the publishing year instead of the complete date, then we can use the DATE_PART() function with the CONCAT() function:

SELECT CONCAT(article_title, ' published in ', DATE_PART('YEAR', published_date))
FROM article_details;

In this example, we utilized the DATE_PART() function that will extract the publishing year from the published_date column:

In this way, you can use any built-in function with the CONCAT() function to achieve different functionalities.

How to Use CONCAT_WS() Function in PostgreSQL?

CONCATE_WS() is an extension of the CONCAT() function that allows us to add a separator between the concatenated strings. In the CONCAT_WS() function, the term “WS” represents “with a separator”. So, the COCAT_WS() function accepts multiple strings along with a separator and returns a concatenated string separated with a specific separator. Here is the syntax of the Postgres CONCAT_WS() function:

CONCAT_WS(separator, string_1, string_2,..., string_n);

In place of a separator, you can use any separator/string. Let’s understand this concept using an example.

Example: How to Use CONCAT_WS() Function on Table’s Data?

In this example, we will separate the concatenated values with a comma:

SELECT CONCAT_WS(', ', article_title, published_date)
FROM article_details;

In this query, we specified “,” as a separator. So, the concatenated string will be separated by a comma:

This time, the values of both columns are separated with a comma. This is how the CONCAT_WS() function works in PostgreSQL.

That was all the necessary information regarding CONCAT() and CONCAT_WS() functions.

Conclusion

CONCAT() and CONCAT_WS() are built-in functions in PostgreSQL that concatenate several strings. It accepts multiple strings as arguments and concatenates them into one string. COCAT_WS() accepts multiple strings along with a separator and returns a concatenated string separated with a specific separator. This post described the working of the CONCAT() and CONCAT_WS() functions using suitable examples.