Another useful function, albeit one that's less frequently applied, is
It returns a
NULL if both
y are equal. Otherwise, it returns the first argument. For example,
NULLIF(5, 4) returns
NULLIF(5, 5) returns
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.