How to Perform the PostgreSQL Coalesce in ObjectRocket Instance

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

Introduction

The Postgres COALESCE function can accept an unlimited number of arguments without constraints and evaluates arguments until locating the first non-null argument. The remaining arguments are not evaluated once a non-null argument is found, however, it will return null if all of the arguments are null. This tutorial will explain how to perform the PostgreSQL COALESCE in an ObjectRocket instance. Note that the latest version of PostgreSQL is required to perform the examples in this tutorial.

Prerequisites

The following prerequisites are required to execute the examples covered in this tutorial:

  • A PostgreSQL instance must be set up in the ObjectRocket Mission Control Panel web page, as shown in the following screenshot:

Screenshot of the PostgreSQL instance in ObjectRocket

  • The latest version of PostgreSQL must be properly installed on the local device. Execute the following psql -V command in the terminal to verify the currently installed version of Postgres:
1
psql (PostgreSQL) 12.2 (Ubuntu 12.2-2.pgdg18.04+1)
  • A basic working knowledge in database management skills or SQL commands is required.

Access the Psql in ObjectRocket Instance

Access the PostgreSQL instance in the ObjectRocket instance by opening the SQL shell for a Windows system or the terminal if using a Linux machine or MacOS.

Next, copy the credentials in the syntax of the PostgreSQL interactive shell, which are psql.

Now execute the following command to access Postgres:

1
sudo su - PostgreSQL

Next, as shown here, login with the PostgreSQL credentials in the ObjectRocket instance:

1
psql -U username -h ingress.hkybrhnz.launchpad.objectrocket.cloud -p 4123

The credentials on the PostgreSQL instance can be seen by clicking on the in the -kbd-VIEW MORE DETAILS-/kbd- button.

Next, click on the -kbd-CONNECT-/kbd- tab.

Now created a username with a strong password.

The PostgreSQL Coalesce Function

This PostgreSQL coalesce function handles and accepts a variety of arguments without constraints. It will return the first statement that is not null. If all of the arguments are null, then the display message well also be null. This is shown in the following example:

1
SELECT COALESCE(5, NULL, 2);

The results will look like this:

1
2
3
4
 coalesce
----------
        5
(1 row)
  • Here the value of “5” is returned because the second argument has a null value and the function will display the first non NULL value at the beginning of the argument, in this case five.

NOTE: The coalesce function interprets multiple null values until it locates the first value of the parameter before it will return the output.

If the COALESCE function is used with a parameter that does not match the value, an error message will be displayed.

Use the PostgreSQL Coalesce Function in ObjectRocket Instance

This example will use the database demo with the table resembling the following:

1
2
3
4
5
6
                       Table "public.test"
 Column |         Type          | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
 id     | integer               |           | not null |
 str    | character varying(25) |           |          |
 int    | integer               |           |          |

Here a null value can be prevented from occurring inside the table by using the coalesce function in PostgreSQL.

The recorded values will be as follows if the SELECT * FROM test; command is used:

1
2
3
4
5
6
7
8
 id  |      str       | int
-----+----------------+-----
 123 | sample message |   1
 124 | hello world    |   2
 125 | welcome        |   3
 126 | demo           |   4
 127 | tutorial       |    
(5 rows)

Now execute the following COALESCE function in the PostgreSQL instance in ObjectRocket to create a value inside the table’s int column:

1
SELECT str, COALESCE(INT, 5);

The syntax will now change the null value with a given value specified in the COALESCE function. This is because the statement will search for a null value until it reaches the last value.

Conclusion

This tutorial covered how to perform the PostgreSQL Coalesce in ObjectRocket instance. The tutorial first explained how to access the PostgreSQL instance in the ObjectRocket instance for Windows, Linux and MacOS. The article then covered how to use the PostgreSQL coalesce function in ObjectRocket instance and provided examples with sample tables. Keep in mind that the coalesce function will continue to interpret multiple null values until it locates the first value of the parameter before it will return the output.

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.