NULLIF

NULLIF(argument_1, argument_2) returns NULL when the two arguments are equal, and returns argument_1 when they are not. This is the inverse of COALESCE: instead of replacing null with a value, NULLIF converts a specific value back into null.

Basic NULLIF examples

SELECT NULLIF(1, 1);    -- returns NULL (arguments are equal)
SELECT NULLIF(1, 0);    -- returns 1   (arguments differ)
SELECT NULLIF('A', 'B'); -- returns 'A' (arguments differ)

Normalizing empty strings to NULL

Databases often contain a mix of actual NULL values and empty strings '', both semantically meaning "no value." Use NULLIF to unify them so COALESCE can apply a single fallback:

CREATE TABLE posts (
  id         SERIAL PRIMARY KEY,
  title      VARCHAR(255) NOT NULL,
  excerpt    VARCHAR(150),   -- may be NULL or empty string
  body       TEXT
);

INSERT INTO posts (title, excerpt, body) VALUES
  ('Post 1', 'Short excerpt', 'Full body text 1'),
  ('Post 2', '',              'Full body text 2'),  -- empty string
  ('Post 3', NULL,            'Full body text 3');  -- actual NULL

-- Without NULLIF: post 2 excerpt shows as '' not the body fallback
SELECT id, title, COALESCE(excerpt, LEFT(body, 40)) AS display
FROM posts;

Output (post 2 still shows empty):

 id |  title  |      display
----+---------+---------------------
  1 | Post 1  | Short excerpt
  2 | Post 2  |                      -- empty string, not body
  3 | Post 3  | Full body text 3

Wrap with NULLIF to treat empty strings as null:

SELECT id, title,
  COALESCE(NULLIF(excerpt, ''), LEFT(body, 40)) AS display
FROM posts;

Output (post 2 now falls back to body):

 id |  title  |      display
----+---------+---------------------
  1 | Post 1  | Short excerpt
  2 | Post 2  | Full body text 2
  3 | Post 3  | Full body text 3

Preventing division-by-zero

Dividing by zero raises an error in PostgreSQL. Wrap the denominator with NULLIF(..., 0) to convert zero into null, making the division return null instead of throwing an error:

-- Calculate male/female ratio; returns NULL when no females exist
SELECT
  (SUM(CASE WHEN gender = 1 THEN 1 ELSE 0 END)
   / NULLIF(SUM(CASE WHEN gender = 2 THEN 1 ELSE 0 END), 0)
  ) * 100 AS "Male/Female ratio"
FROM members;

When no female members exist, the denominator becomes NULL and the result is NULL rather than an error.

Practical tips

• The most common pattern is COALESCE(NULLIF(col, ''), fallback) to handle both NULL and empty string in one step.
• Use NULLIF(denominator, 0) in every division expression where the denominator might be zero.
• Both arguments to NULLIF must be of compatible types; PostgreSQL will raise an error if they differ significantly.
• NULLIF evaluates argument_1 twice internally, which rarely matters but is relevant if the expression has side effects.