How to Perform the PostgreSQL Coalesce in ObjectRocket Instance
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.
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:
- The latest version of PostgreSQL must be properly installed on the local device. Execute the following
psql -Vcommand in the terminal to verify the currently installed version of Postgres:
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
Now execute the following command to access Postgres:
sudo su - PostgreSQL
Next, as shown here, login with the PostgreSQL credentials in the ObjectRocket instance:
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:
SELECT COALESCE(5, NULL, 2);
The results will look like this:
- 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.
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:
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:
id | str | int
123 | sample message | 1
124 | hello world | 2
125 | welcome | 3
126 | demo | 4
127 | tutorial |
Now execute the following
COALESCE function in the PostgreSQL instance in ObjectRocket to create a value inside the table’s int column:
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.
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