Understanding the CockroachDB COALESCE Function

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

Introduction

When you’re dealing with a list of values in CockroachDB, there’s a chance that some of those values may be NULL. The CockroachDB COALESCE function returns the first non-NULL expression in a list, offering a simple way to handle NULL values in your data. In this article, we’ll discuss the COALESCE() function and look at some examples to show you how it works.

Prerequisites

Before you proceed with this tutorial, make sure that CockroachDB is installed and configured on your machine. The \c command that you may have used in psql will not work in the Cockroach SQL CLI; instead, you can execute the USE command to connect to a database:

1
USE test_db;

CockroachDB Connect to Database Example

Let’s begin by accessing a database in CockroachDB. Open a terminal or command prompt window, and enter the following command to connect to a database named test_db:

1
cockroach sql -d test_db --insecure --host=localhost:26257

Cockroachdb connect to database example for the Cockroachdb coalesce function

CockroachDB COALESCE Function

The COALESCE() function takes a list of parameters delimited by commas, and it returns the first argument that is not NULL. If the first argument is NULL, for example, it will proceed through the list, moving from left to right, until it finds a non-NULL argument. If there is no non-NULL argument in the list, the function will return NULL.

Shown below is the syntax of COALESCE function:

1
SELECT COALESCE (column_name) FROM TABLE_NAME;

CockroachDB SELECT COALESCE Examples

In the following example, we can see how the COALESCE() function returns the first argument that is not NULL:

1
2
3
4
5
SELECT COALESCE (10, 20);
  COALESCE
------------
        10
(1 ROW)

We can see that the COALESCE() function skips all NULL arguments in a list:

1
2
3
4
5
SELECT COALESCE (NULL, 10, 20);
  COALESCE
------------
        10
(1 ROW)

We got 10 as result because it skipped the NULL value, and the 10 was the first non-NULL value in the list.

Let’s check out another example.

1
2
3
4
5
SELECT COALESCE (NULL, NULL, 20);
  COALESCE
------------
        20
(1 ROW)

We got 20 as result because the first two values in the list were NULL values.

Next, let’s try to use the CockroachDB COALESCE function with a table. We’ll create a table named items to use in this example:

1
2
3
4
5
6
CREATE TABLE items (
    id INT PRIMARY KEY,
    item VARCHAR NOT NULL,
    quantity INT,
    price NUMERIC
);

Then we’ll insert some records into the table we have just created:

1
2
3
4
5
6
INSERT INTO items (id, item, quantity, price)
VALUES ('1', 'Wrench', NULL, '100'),
('2', 'Saw', '13', '145'),
('3', 'Hammer', NULL, '170'),
('4', 'Chisel', '54', '120'),
('5', 'Screwdriver', '24', '90');

We can use a SELECT statement to view the contents of our items table:

1
2
3
4
5
6
7
8
9
SELECT * FROM items;
  id |    item     | quantity | price
-----+-------------+----------+--------
   1 | Wrench      |     NULL |   100
   2 | Saw         |       13 |   145
   3 | Hammer      |     NULL |   170
   4 | Chisel      |       54 |   120
   5 | Screwdriver |       24 |    90
(5 ROWS)

Now that our table is ready, let’s proceed with our next example:

For this example, we’ll get the total price of each item by multiplying the price by the quantity of each item. We can do this using the following statement:

1
SELECT item, (quantity * price) AS total_price FROM items;

The output of this statement is shown below:

1
2
3
4
5
6
7
8
     item     | total_price
--------------+--------------
  Wrench      | NULL
  Saw         |        1885
  Hammer      | NULL
  Chisel      |        6480
  Screwdriver |        2160
(5 ROWS)

We now have the total price for each item, but we don’t want any NULL values to be on the list. To do that, we can use the COALESCE() function on the column that contains NULL value. In our table, it’s the column quantity that has NULL values in it.

The following SQL statement example uses the COALESCE() function to return 0 as a column value instead of NULL:

1
2
SELECT item, (COALESCE(quantity, 0) * price)
AS total_price FROM items;

The following output would be returned from this statement:

1
2
3
4
5
6
7
8
     item     | total_price
--------------+--------------
  Wrench      |           0
  Saw         |        1885
  Hammer      |           0
  Chisel      |        6480
  Screwdriver |        2160
(5 ROWS)

If the COALESCE() function finds a NULL value, it will skip it and return 0 instead.

Conclusion

It’s important to handle NULL values correctly when you receive the results of a CockroachDB query. The CockroachDB COALESCE function can be used to skip NULL values in a list or replace the NULLs with a different value. In this article, we explained how the COALESCE() function works and provided several examples of its use. With our examples to get you started, you’ll be able to make use of the COALESCE() function in your own CockroachDB 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.