PostgreSQL database stores data in the form of tables. There is a great possibility that the database tables contain unnecessary and extra columns, that can be created as one. For example, in some cases, where the city or the location is not that important, there is no need to separate 2 columns for address and city. We can simply create one column that may be named “address” which contains the detailed address and the city. This can be done by concatenating the two columns.

In this post, we will see a couple of ways to concatenate columns in PostgreSQL:

● Concatenating Table Columns Using the CONCAT() function.

● Concatenation of two different data types tables using the “||” operator.

Let’s get started with the article.

How to Concatenate Columns in PostgreSQL Using CONCAT() Function?

In PostgreSQL, the CONCAT() function is generally used to concatenate/combine two or more strings, taken as arguments. This function can also be utilized to combine two or more columns in Postgres. The columns that we want to concatenate are passed into the CONCAT() function as arguments. The basic syntax to concatenate columns can be written as

SELECT *, CONCAT(col_1,col_2,... col_n) AS <new_col_name> FROM tab_name;

In the above syntax:

● The names of columns that we want to concatenate are passed into the CONCAT() function in sequence as parameters.

● After the AS keyword, the name of the new column is specified which is returned as a result of concatenation.

● The name of the targeted table will be written after the FROM statement.

Let’s see how the CONCAT() function works with the help of examples.

Example: Concatenate Two Columns in PostgreSQL

We can concatenate two columns in Postgres using the CONCAT() function. Let's consider the following table named “registration” containing the columns “studentid”, “studentname”, “address”, “city”, and “phone_no” of each student. The table is as follows:

Now here we can combine the address and city columns in a single column named “complete_address”. The query will be written as:

SELECT *, CONCAT(address, city) as complete_address from registration;

After execution of this query, we will see that another column is created that has both the columns concatenated like this:

We can also concatenate the columns using some character such as a hyphen or underscore between them. The query for this can be customized as:

SELECT *, CONCAT(address,' - ',city) as complete_address from registration;

The query will add the “-” between the entries of both columns.

We will now see how the concatenation is done in the case of two columns of different data types.

How to Use || Operator to Concatenate Columns?

We can also concatenate two columns using the “||” operator. However, the additional function this operator offers is that we can concatenate the two columns with different data types. This is done by typecasting one of them. For example, in the above-considered case, the “studentname” column is of character data type, and the “Phone_no” is an integer. So if we want to concatenate these columns we will write the query like this:

SELECT *, studentname || Phone_no::text AS   student_ph FROM   registration;

In the above code:

● The “studentname” column had the character data type.

● The “Phone_no” column had an Integer data type.

● So we are basically typecasting the “Phone_no” column into the character/text data type before concatenating it with the other column.

● A new column, named “student_ph”, will be formed.

The output of the above query will be as follows:

This is how we can concatenate two columns in PostgreSQL.

Conclusion

To concatenate multiple columns in PostgreSQL, the CONCAT() function is used. Moreover, we can also use the concatenation operator “||”. The CONCAT() function takes the columns to be concatenated as parameters. We can also specify any special symbol using which we want to concatenate the columns. Postgres allows us to concatenate the columns with different data types by typecasting them first. In this article, we have learned about the concatenation of columns in PostgreSQL and discuss both the concatenation cases with examples.