How to Use the Postgres CONCAT Function

Introduction

When you’re working with string data in PostgreSQL, it’s important to know how to manage and manipulate it to meet your needs. The CONCAT function is a helpful tool that allows you to concatenate two or more strings in a result set. In this article, we’ll show you how to use the Postgres CONCAT function to join string data from different columns.

Prerequisite

Before diving into the examples in this article, make sure that PostgreSQL is installed and configured on your machine.

What is PostgreSQL CONCAT?

The Postgres CONCAT function allows us to combine two or more strings to produce a meaningful string as a result. This built-in PostgreSQL function is a powerful formatting tool when you’re working with tables that contain string data.

Keep in mind that the Postgres CONCAT function can only be used with character strings. Strings can also be concatenated by putting the (||) concatenation operator between them; this method results in a combined compound string that can be more readable and meaningful in a result set.

Shown below is the simplest form of the CONCAT function:

1
CONCAT(str_1, str_2, ...)

As you can see, the CONCAT function accepts a list of arguments, with each argument having a string format.

Creating Sample Database

Now that we have a basic understanding of the Postgres CONCAT function, we’ll proceed to create the sample dataset that we’ll use in our examples.

First, we’ll create a database:

1
CREATE DATABASE sampleperson;

Then we’ll create a table:

1
2
3
4
5
6
CREATE TABLE person (
    id INT PRIMARY KEY,
    name VARCHAR (100),
    lastname VARCHAR (100),
    age INT
);

After we create the table, we can insert some records:

1
2
3
4
5
6
7
8
9
10
INSERT INTO person (id, name, lastname, age)
VALUES
   (1, 'yeshua', 'galisanao',9 ),
   (2, 'abi', 'galisanao', 3),
   (3, 'raizel', 'galisanao', 16),
   (4, 'risa', 'galisanao', 37),
   (5, 'rommel', 'galisanao', 37),
   (6, 'anita', 'galisanao', 62),
   (7, 'ruben', 'galisanao', 74),
   (8, 'ruby', 'galisanao', 40);

Our table will look like this:

1
2
3
4
5
6
7
8
9
10
11
 id |  name  | lastname  | age
----+--------+-----------+-----
  1 | yeshua | galisanao |   9
  2 | abi    | galisanao |   3
  3 | raizel | galisanao |  16
  4 | risa   | galisanao |  37
  5 | rommel | galisanao |  37
  6 | anita  | galisanao |  62
  7 | ruben  | galisanao |  74
  8 | ruby   | galisanao |  40
(8 rows)

Postgres CONCAT example

In this section, we’ll look at how to use the CONCAT function in PostgreSQL.

We’ll be using the following query:

1
2
3
4
SELECT
   CONCAT  (name, ' ', lastname) AS "Full name"
FROM
   person;

The output should look something like this:

1
2
3
4
5
6
7
8
9
10
11
    Full name
------------------
 yeshua galisanao
 abi galisanao
 raizel galisanao
 risa galisanao
 rommel galisanao
 anita galisanao
 ruben galisanao
 ruby galisanao
(8 rows)

Here’s another example. This time, we’re using the ‘||’ operator:

1
2
3
4
SELECT
   CONCAT  (name || NULL) AS "Full name"
FROM
   person;

The output should look like this:

1
2
3
4
5
6
7
8
9
10
11
Full name
-----------








(8 rows)

As you can see, Postgres returned eight rows of NULL values– what happened? The results are all NULL because the operation adhered to the SQL standard that any expression involving a NULL value will result in a NULL value. In these cases, it’s better to use the CONCAT function, which was introduced in version 9.1. Unlike the || operator, the CONCAT function simply ignores NULL values.

Postgres CONCAT in a Function

Let’s look at how we can use CONCAT in a function:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE FUNCTION Fullname (text, text) RETURNS text AS '
    DECLARE
   
        -- Declare aliases for the two function arguments.
       name ALIAS for $1;
       lastname ALIAS for $2;
        -- we declare a text variable that will hold the resulting string of the concatenation
       result text;
    BEGIN
        -- We combine the name and lastname variable
       result := CONCAT(name, lastname);
       return result;
   
    END;
'
LANGUAGE 'plpgsql';

To test out our application, we can use the following command:

1
SELECT Fullname('Gerard ','Buttler');

The output should look like the following:

1
2
3
4
    fullname
----------------
 Gerard Buttler
(1 row)

Conclusion

When you want to present the results of a query in a clear and organized manner, concatenating strings can go a long way toward making your results more readable. In this article, we learned how the PostgreSQL CONCAT function works and looked at a few examples of its use. With these examples to guide you, you’ll be able to manipulate and concatenate string data in your own PostgreSQL database.

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.