How to Use PostgreSQL Coalesce Function with Examples
How to Use PostgreSQL Coalesce Function with Examples. Accelerate your SQL professional career with the PostgreSQL COALESCE post. This post guides experienced SQL professionals/ beginners to expedite their database careers. We have decided to focus more on the core concepts of PostgreSQL functions. In this tutorial, you will be learning one of the important functions of PostgreSQL is COALESCE. Are you excited to explore more on the PostgreSQL COALESCE function? Then let’s get started.
Before getting into the functions of the PostgreSQL concept, we would like to give a quick introduction to the PostgreSQL.
Introduction to the PostgreSQL
PostgreSQL is an open source, advanced, and business driven database management system. PostgreSQL database supports both JSON (javascript object notation) as well as relational database management server language (SQL).
Features of PostgreSQL
- PostgreSQL database language is a highly stable one that has a history of more than 20 years.
- In recent times, most SQL developers prefer to use PostgreSQL in the development of web and mobile applications.
- It is a widely used primary database management system because of its user friendly features.
- PostgreSQL is a robust database server so the users can run them on any type of operating system (Windows, Linux, and UNIX, etc.).
- It supports many programming languages like Python, Java, C, C++, and .net.
PostgreSQL COALESCE function
PostgreSQL COALESCE function returns the null as well non functional arguments. The basic syntax of the PostgreSQL COALESCE function is given below;
COALESCE (argument_1, argument_2, …..argument_n);
Use cases for PostgreSQL COALESCE functions
- The COALESCE function returns the unlimited (infinite) number of arguments. But it does not return the first argument as a null value. Suppose if all the arguments are null values, then the entire statement will be the “NULL” value.
- The COALESCE function first evaluates all the arguments from left to the right until it finds any NULL value, it stops immediately evaluating the remaining arguments.
- The COALESCE function offers the same functionalities as“NVL” or “IFNULL” functions used in the SQL system.
Note: MySQL uses the “IFNULL” function whereas SQL uses the “NVL” function.
PostGreSQL COALESCE function example
Here is the COALESCE function example that works with a non NULL value for the first parameter:
SELECT COALESCE (1, 2);
The output is as follows:
Coalesce 1
Here is how COALESCE function works when a NULL value is the first parameter:
SELECT COALESCE (Null, 2, 1);
The output is as follows:
Coalesce 2
We often use the PostgreSQL COALESCE function to substitute( inject) the NULL value while querying the data.
SELECT
COALESCE (excerpt, RIGHT(Content, 150)) //substituting 150 data value
FROM
Post;
Prevent NULL values with COALESCE
First, we need to create a table name “item1” by using create table statements:
CREATE TABLE item1 (
ID serial PRIMARY KEY,
PRODUCT Varchar (150) NOT NULL,
Price_val NUMERIC NOT NULL,
Discount_val NUMERIC
);
A brief description of the fields is shown below:
- ID: it is defined as a primary key that identifies the items in the given table.
- Product: this field defines the product name.
- Price: This field defines the product price.
- Discount: this field defines the discount of the product.
Now, insert the values to the above item table:
INSERT into items1 (product, price_val, discount_val)
VALUES
(‘A’, 2000, 20),
(‘B’, 2500, 25),
(‘C’, 3000, 5),
(‘D’, 650, NULL);
Next, query the net price of the product:
SELECT
product,
(price_val-discount_val) AS net_price
FROM
items1;
The output is as follows:
Product net_price
A 1980
B 2475
C 2995
D NULL
You can observe that the D value has become NULL, the entire PostgreSQL net_price returns a NULL value.
To avoid such a situation, we can use COALESCE function:
SELECT
Product,
(price_val - COALESCE (discount_val, 0)) AS net_price
FROM
items;
After adding COALESCE function, the output is as follows:
Product net_price
A 1980
B 2475
C 2995
D 650
Now you can see the difference the D value has written 650, this is because we have used ZERO instead of a NULL value while calculating the net_price of the product.
PostgreSQL COALESCE function with CASE statement
Here we can use the PostgreSQL COALESCE function with CASE statement to handle the NULL value. The below query statement is an example of how to use a CASE statement in the COALESCE function:
SELECT
Product,
(
Price -CASE
WHEN discount_val is NULL THEN
0
ELSE
discount_val
END
) AS net_price
FROM
items;
In the above query statement example, if the discount is NULL then use the ZERO value otherwise directly use the discount value in the statement that calculates the product’s net price.
Note: When it comes the performance, both COALESCE and CASE functions are the same. Most developers prefer to use COALESCE function instead of the CASE because the COALESCE function makes the query easier and shorter.
PostgreSQL COALESCE function in Where clause
The PostgreSQL COALESCE function in where clause syntax is as follows:
WHERE COALESCE (arg1, arg2)<> ‘1’
In this case, the COALESCE function first returns the non value data from the given list of the arguments. If you have assigned given arguments as a NULL value then it will return the “0”. In a few scenarios of WHERE clause cannot accept the numeric value “0” so instead of using “0” use “ZERO” while defining the argument lists.
If you want to get the department list, follow the syntax:
WHERE COALESCE (A.dept, B.dept) <> ‘1’ NULL
If you wish not to use COALESCE function, NULL values become equal or unequal when compared to other arguments in the WHERE clause, this is because the NULL value is UNKNOWN.
If you want to fetch only department record lists, then modify the above syntax as follows:
WHERE A.department <>1
If you want to fetch the records from department A which is not NULL or equal to ‘1’. Then go with the following statement:
WHERE COALESCE (A. dept, B.dept) <> ‘1’
If you wish not to use COALESCE or IsNULL value, the following statement is as follows:
WHERE 1 in (A.dept, B.dept)
Now you want to fetch records only from department A and make the department B as NULL, then go with the following statement:
WHERE COALESCE (A.dept, 0) <> ‘1’
Differences between COALESCE and ISNULL functions in PostgreSQL
The following table gives a complete idea of the differences between the COALESCE and ISNULL:
| ISNULL | COALESCE |
|---|---|
|
ISNULL is not an ANSI function, it is a T-SQL function. |
Whereas COALESCE is an ANSI standard function. |
|
ISNULL function is available only in Microsoft SQL systems. |
COALESCE function is available in almost all the database management systems such as Oracle, PeopleSoft, and MySQL. |
|
ISNULL is a less flexible function than COALESCE function. |
Whereas COALESCE function is more flexible and powerful. |
|
ISNULL supports exactly 2 parameters at a time. |
COALESCE function supports less than 2 parameters. |
Final Thoughts on using PostgreSQL Coalesce Function
From this PostgreSQL COALESCE function post, we have tried our best to explain the core concepts of COALESCE functions, and how to use them with various conditional statements. We hope this post helps many database professionals to enhance their knowledge of the PostgreSQL functions and methods using a PostgreSQL Server. This post may also be helpful for several SQL communities, and online experts who want to upgrade their SQL skillsets.
