PostgreSQL Concatenate Function for Joining Strings

Have a Database Problem? Speak with an Expert for Free
Get Started >>

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:

1
sudo su - postgres

Then, use the psql command to enter the interface:

1
psql

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:

1
CREATE DATABASE test_db;

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:

1
2
CREATE TABLE TABLE_NAME (column_name + DATA TYPE +
    CONSTRAINT [optional]);

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:

1
2
INSERT INTO TABLE_NAME (COLUMN_1,COLUMN_2...,)
                 VALUES (VALUES_1,VALUES_2...,)

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:

1
2
3
4
5
6
7
INSERT INTO
  users(id, first_name, last_name, age, address)
VALUES
  (1, 'justine', 'bibe', 20, 'america'),
  (2, 'kimmy', 'david', 29, 'spain'),
  (3, 'josh', 'bieber', 25, 'korea');
INSERT 0 3

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

1
2
3
4
5
6
7
SELECT * FROM users;
 id | first_name | last_name | age | address
----+------------+-----------+-----+---------
  1 | justine    | bibe      |  20 | america
  2 | kimmy      | david     |  29 | spain
  3 | josh       | bieber    |  25 | korea
(3 ROWS)

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:

1
2
3
4
SELECT
    CONCAT (first_name, ' ', last_name) AS "complete name"
FROM
    users;

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

Our result will look like this:

1
2
3
4
5
6
 complete name
---------------
 justine bibe
 kimmy david
 josh bieber
(3 ROWS)

Screenshot of PostgreSQL concatenate CONCAT function example SQL statement

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:

1
2
3
4
SELECT CONCAT_WS(', ',first_name, last_name)
    AS complete_name
FROM users
    ORDER BY first_name;

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

1
2
3
4
5
6
  complete_name
---------------
 josh, bieber
 justine, bibe
 kimmy, david
(3 ROWS)

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:

1
2
SELECT CONCAT ('object', NULL)
            AS result_string;

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:

1
2
3
4
SELECT last_name,
concat ('your name is composed of ',
LENGTH (last_name),' characters')
FROM users;

The CONCAT function will return the following result:

1
2
3
4
5
6
 last_name |                concat
-----------+---------------------------------------
 bibe      | your name IS composed OF 4 characters
 david     | your name IS composed OF 5 characters
 bieber    | your name IS composed OF 6 characters
(3 ROWS)

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.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.