NULLIF in PostgreSQL

NULLIF(argument_1, argument_2) returns NULL when the two arguments are equal, and returns argument_1 otherwise. It is the inverse of COALESCE: instead of replacing NULL with a value, NULLIF converts a specific value back into NULL. The two functions are often combined to handle both real NULL values and sentinel values like empty strings or zeros.

Syntax

NULLIF(argument_1, argument_2)

Basic scalar 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)
SELECT NULLIF('', '');   -- returns NULL (empty strings match)

Practical Example

An events table where the venue column may be stored as either NULL or an empty string—semantically both mean “no venue assigned”:

CREATE TABLE events (
  id         SERIAL PRIMARY KEY,
  title      VARCHAR(200) NOT NULL,
  venue      VARCHAR(200),    -- may be NULL or ''
  description TEXT
);

INSERT INTO events (title, venue, description) VALUES
  ('Product Launch',   'Convention Center',   'Annual product reveal'),
  ('Team Offsite',     '',                    'Remote team gathering'),
  ('Board Meeting',    NULL,                  'Quarterly review'),
  ('Dev Conference',   'Tech Hub',            'Engineering summit');

-- Without NULLIF: empty string '' and NULL are treated differently
SELECT id, title, COALESCE(venue, 'TBD') AS display_venue
FROM events;

 id |      title      |      display_venue
----+-----------------+--------------------
  1 | Product Launch  | Convention Center
  2 | Team Offsite    |                    -- empty string slips through
  3 | Board Meeting   | TBD
  4 | Dev Conference  | Tech Hub

Use NULLIF to treat '' the same as NULL:

SELECT
  id,
  title,
  COALESCE(NULLIF(venue, ''), 'TBD') AS display_venue
FROM events;

 id |      title      |    display_venue
----+-----------------+------------------
  1 | Product Launch  | Convention Center
  2 | Team Offsite    | TBD
  3 | Board Meeting   | TBD
  4 | Dev Conference  | Tech Hub

Preventing Division-by-Zero

Dividing by zero raises an error in PostgreSQL. Wrap the denominator with NULLIF(..., 0) to convert zero into NULL, returning NULL rather than failing:

CREATE TABLE orders (
  id          SERIAL PRIMARY KEY,
  product_id  INTEGER NOT NULL,
  quantity    INTEGER NOT NULL,
  total_cost  NUMERIC(10, 2) NOT NULL
);

INSERT INTO orders (product_id, quantity, total_cost) VALUES
  (1, 10, 250.00),
  (2,  0,   0.00),   -- zero quantity edge case
  (3,  5, 125.00);

-- Safe unit cost calculation—returns NULL instead of error when quantity = 0
SELECT
  id,
  product_id,
  quantity,
  total_cost,
  ROUND(total_cost / NULLIF(quantity, 0), 2) AS unit_cost
FROM orders;

 id | product_id | quantity | total_cost | unit_cost
----+------------+----------+------------+-----------
  1 |          1 |       10 |     250.00 |     25.00
  2 |          2 |        0 |       0.00 |      NULL
  3 |          3 |        5 |     125.00 |     25.00

Inspect which rows would be affected without the guard:

SELECT id FROM orders WHERE NULLIF(quantity, 0) IS NULL;

Testing with Vela

Data cleaning code that depends on NULLIF is sensitive to the actual mix of NULL, empty string, and zero values in production. Clone a production snapshot to a Vela branch to validate your NULLIF / COALESCE chains against real column distributions before rolling out schema changes or ETL updates.

Production Tips

• The canonical null-safe string pattern is COALESCE(NULLIF(col, ''), fallback)—it handles both actual NULL and empty string in one expression.
• Guard every division expression with NULLIF(denominator, 0), especially in aggregations over user-supplied data where zero denominators are plausible.
• NULLIF evaluates argument_1 twice internally. This is rarely a concern but matters if the argument is a function with side effects.
• Both arguments must be of compatible types. Use explicit CAST when combining a text column with an integer sentinel to avoid type errors.
• Combine with EXPLAIN (ANALYZE) to verify that null guards in WHERE clauses do not accidentally disable index scans on frequently queried columns.