Another useful function, albeit one that's less frequently applied, is NULLIF(x, y).
It returns a NULL if both x and y are equal. Otherwise, it returns the first argument. For example, NULLIF(5, 4) returns 5, while NULLIF(5, 5) returns NULL.
Why would you use this function? Because it helps you avoid division by zero. Consider this example:
We have $10,000 and want to give an equal amount to a number of people. There's a number stored in the all_people column. However, some people are rich and don't need the money, and their number is stored in the rich_people column. Now, if we want to decide how much a single 'not-rich' person should receive, we could write:
10000 / (all_people - rich_people)
This query works fine, unless all the people are rich. We will then get a 0 in the denominator.
As you know, we can't divide by zero, so the database will return an error. We can use the NULLIF() function to solve this problem:
10000 / NULLIF(all_people - rich_people, 0)
NULLIF() checks if the subtraction equals 0. If it does equal 0, the function returns a NULL; otherwise it just gives the result of the subtraction.
Dividing by zero is illegal in PostgreSQL, but dividing by NULL will just give a NULL. In this way, we can prevent our database from producing an error.
Today, Home made yours has a special offer: the price of each product has decreased by $1.99. This means some products may be free!
A customer has $1,000.00 and would like to know how many of each product he could buy. Show the name of each product and the quantity he could buy (i.e., divide 1000.00 by the new price of each product).
In case of division by zero, return a NULL. Most products will have a decimal part, but don't worry about it.
The new price is calculated as follows:
price - 1.99
Use it as the first argument of NULLIF().
