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.