Announcing our $3.4M seed round 🚀 Read more →

How to Use nullif() in PostgreSQL

The nullif() function returns a null value, if a the value of the field/column defined by the first parameter equals that of the second. Otherwise, it will return the original value. Here's an example below:

select
  name,
  platform,
  nullif(platform,'Did not specify') as platform_mod
from users;
   name    |    platform     | platform_mod
-----------+-----------------+------------
 Steve     | Mac             | Mac
 Bill      | Windows         | Windows
 Linus     | Linux           | Linux
 Beth      | Did not specify |

Note that nullif() is only capable of replacing one value with null. If you need to replace multiple values, you can use the CASE function.

select
  name,
  platform,
  case
    when platform = 'Mac' then null
    when platform = 'Windows' then null
    when platform = 'Linux' then null
    else platform
  end as platform_mod
from users;
   name    |    platform     | platform_mod
-----------+-----------------+------------
 Steve     | Mac             |
 Bill      | Windows         |
 Linus     | Linux           |
 Beth      | Did not specify | Did not specify

Ready for a modern SQL editor?