In PostgreSQL, dividing an integer by zero can lead to a zero division error, which can cause problems in database operations. However, PostgreSQL provides useful methods to handle this error effectively.
We’ll learn various techniques like using the NULLIF() function and the CASE statement to address zero division errors. By understanding and implementing these methods, we can ensure smooth and error-free arithmetic operations in your PostgreSQL database.
How to avoid division by zero?
In Postgres, we can perform arithmetic operations like addition, subtraction, multiplication, and division. When an integer is divided by Zero it throws a zero division error and we have to solve the zero division error by ourselves in PostgreSQL. There are methods available that are super useful for handling zero-division errors. Some of the methods are as follows:
- Using NULLIF() Function
- Using Case Statement to Handle Zero Division Error
Look at the below image to understand what zero zero-division error looks like.
Zero Division
Explanation: As we can see in the above image, when we try to divide 10 by Zero we get an error, but handling these errors is important. When working on large projects it is important to solve this arithmetic error or it will create havoc later on.
Methods to Handle Zero-Division Error
Some methods are useful to handle zero division error, and for that, you should have some sound knowledge of working with Postgres. So, now let’s look into these methods to handle this obnoxious zero-division error.
1. NULLIF() Function
In Postgres, the NULLIF function takes two expressions and returns NULL if they are equal and if they are not equal it will return the first expression. let’s see in syntax manner.
Syntax:
If Expression 1 == Expression2 then, return NULL
If Expression 1 != Expression 2 then, return Expression 1
Now let’s look into the syntax of NULLIF() and then we will going to use that to handle zero division error.
Syntax of NULLIF():
NULLIF(Expression_1, Expression_2)We are going to use NULLIF() to handle zero division errors and then see different examples to use this function using our records in the table.
Example 1:
We are going to see how we can use NULLIF() to handle zero division errors while doing arithmetic operations.
Query:
SELECT 10 / NULLIF(0,0)Output:
NULLIF()
Explanation:
- As we can see, we don’t get a zero division error this time. Here Expression 1 is zero and Expression 2 is also Zero thus we get NULL value in return.
- But here, we are facing a problem, as we got a NULL value we can not output anything at all. It is just showing a NULL value.
- To solve this issue we have to use a Keyword called COALESCE.
Here we are not able to print results after handling zero division error. To overcome this issue we have to use COALESCE.
COALESCE(): It allows to have a default value in case the first one is not present or not available. We are going to use this with our NULLIF() and then you will understand it better.
Example 2: COALESCE with NULLIF()
Query:
SELECT COALESCE ( 10 / NULLIF(0,0), 0 );Output:
Handling error
Explanation:
- Now, we have successfully handled zero division errors using COALESCE() and NULLIF().
- Here, we have passed two arguments in COALESCE(), first argument is “10 / NULLIF(0,0)”. The second argument we have passed is “0″.
- As COALESCE() returns first not NULL value, and here “NULLIF(0,0)” returns 0.
- So COALESCE() neglects the value returned by NULLIF(0,0) that is NULL, and returns the value given in the 2nd argument which is value “0”.
- That’s why we got “0″ instead of NULL this time.
2. Case Statement to Handle Zero Division Error
The case statement is useful for handling zero-division errors when we are working with records. Let’s look into the syntax of the case statement and then work with it to handle zero division errors.
Syntax:
CASE
WHEN condition..1 THEN result..1
WHEN condition..2 THEN result ..2
END
Explanation:This is the general syntax of the CASE statement we use this statement to overcome zero division error.
Example 1:
Here in this example, we are going to use records to calculate results and handle zero division error for that we have to create a table.
Create a table using the below query.
Query:
CREATE TABLE maths(numerator INT, denominator INT);Now after creating a table insert these values.
Query:
INSERT INTO maths(numerator,denominator) VALUES(12,2);
INSERT INTO maths(numerator,denominator) VALUES(21,0);
INSERT INTO maths(numerator,denominator) VALUES(10,3);
INSERT INTO maths(numerator,denominator) VALUES(15,0);
Output:
Maths Table
Now we are going to use the CASE statement with this table to calculate the values and tackle zero division error. Enter the below query to get the results also tackling zero division error.
Query:
SELECT numerator, denominator, CASE
WHEN denominator <= '0' then 0
WHEN denominator > '0' THEN numerator/denominator
END AS ans
FROM maths;
Output:
Case Statement
Explanation:
- We are using the CASE statement here to match the condition and execute the related result.
- Here to handle zero division error, we check the case if the “denominator <= ‘0’ ” then it will return “0” not the error.
- And, name the column “and” to display the results.
Conclusion
Zero division is almost faced by all individuals who work with databases, and it is not that much complicated to handle and avoid zero division errors in PostgreSQL. The above methods will solve most of the issues for handling zero division errors. There are many ways to construct a CASE statement it depends on how you want to handle the error and get specific results but the structure will remain the same.