PostgreSQL Concatenate Function for Joining Strings

Introduction

When you’re querying data in PostgreSQL, you want your results to be returned in a format that’s easy to read and understand. Joining, or concatenating, strings can help transform the results of a query, making them clear and readable. The PostgreSQL CONCAT() function makes it easy to accomplish this task. In this article, we’ll show you how to use the PostgreSQL concatenate function to join one or more strings into one output string.

Prerequisites

A few key prerequisites need to be in place for this tutorial:

• You’ll need to have PostgreSQL installed on your machine.
• You’ll need to have access to the PostgreSQL command-line interface.
• You should have a basic understanding of database management systems, especially PostgreSQL.

Open command-line interface

In order to execute queries that make use of the PostgreSQL concatenate function, we’ll need to access the Postgres command-line interface.

To start the process, use the command shown below to elevate your privileges to those of the superuser:

Then, use the psql command to enter the interface:

PostgreSQL Concatenate

The PostgreSQL concatenate function CONCAT() allows you to join two or more strings into one output string.

Create Database in PostgreSQL

Let’s start by creating a sample database to use in our examples. We’ll use the following SQL statement:

To connect to the new database, enter \c followed by the name of the database you just created.

Create Table in PostgreSQL

Our next step is to create a table in this new database. The following syntax is used to create a table in PostgreSQL:

Note: The constraint is optional. It can be used to define rules or to limit the type of data in the table.

Insert records in PostgreSQL table

Now that we’ve created a new table in our database, let’s add some records to it. To insert data, the following syntax is used:

In PostgreSQL, you can insert multiple values into a record by using commas to separate the list of columns in the database table. We can see how this works in the INSERT statement below:

To verify that this insert operation was successful, we can perform a simple SELECT statement:

The users table shown above is the sample table we’ll use to demonstrate the CONCAT() function in PostgreSQL.

PostgreSQL concatenate examples

In our example, we’ll show how to concatenate two strings into one using the _firstname and _lastname columns in the users table.

Let’s look at this example:

Notice that we added a space between the first_name and last_name columns.

Our result will look like this:

PostgreSQL concatenate using comma separator example

If you’d like to use a comma as a separator between two strings, use the syntax shown in this example:

In this case, the result will look like the following:

The CONCAT_WS() function call used in this example allows you to pass a special separator or delimiter, such as a comma, when concatenating strings. The “WS” in the function name stands for “with separator”.

PostgreSQL concatenate NULL values example

The next example shows an attempt to concatenate NULL values:

The PostgreSQL concatenate function simply ignores the NULL arguments.

PostgreSQL concatenate using LENGTH function

The LENGTH function in PostgreSQL returns the length of the specified string. You can incorporate the value returned by the LENGTH function in a concatenation of strings:

The CONCAT function will return the following result:

Conclusion

Joining two or more strings into one can help make your PostgreSQL query results easier to read, helping you gain better insights from your data. In this article, we showed you how to use the PostgreSQL concatenate function to join multiple strings into a single output string and showed you examples to illustrate how this function can be used. If you’ve followed along with our examples, you’ll be ready to implement the CONCAT() function in your own PostgreSQL queries.