IS DISTINCT FROM for NULL-safe comparison in PostgreSQL

Introduction

Sometimes you want to compare two values and treat NULLs as real values, not unknowns. IS DISTINCT FROM helps you do that safely.

You want to check if two columns have different values, including when one or both are NULL.

You need to filter rows where a value changed, even if the old or new value is NULL.

You want to avoid confusing results when comparing NULLs with = or <> operators.

You are writing queries that must treat NULL as a real value for comparison.

You want to find rows where two fields are not the same, counting NULLs as different.

Syntax

PostgreSQL

expression1 IS DISTINCT FROM expression2

This operator returns true if the two expressions are different or if one is NULL and the other is not.

It returns false if both expressions are equal or both are NULL.

Examples

Returns false because both values are equal.

PostgreSQL

SELECT 5 IS DISTINCT FROM 5;

Returns true because the values are different.

PostgreSQL

SELECT 5 IS DISTINCT FROM 10;

Returns true because one value is NULL and the other is not.

PostgreSQL

SELECT NULL IS DISTINCT FROM 5;

Returns false because both values are NULL (treated as equal).

PostgreSQL

SELECT NULL IS DISTINCT FROM NULL;

Sample Program

This creates a temporary table with pairs of values including NULLs. Then it checks if each pair is distinct using IS DISTINCT FROM.

PostgreSQL

CREATE TEMP TABLE test_values (a INT, b INT);
INSERT INTO test_values VALUES (1, 1), (2, NULL), (NULL, 3), (NULL, NULL);

SELECT a, b, a IS DISTINCT FROM b AS is_diff FROM test_values ORDER BY a NULLS LAST;

Important Notes

Unlike = or <>, IS DISTINCT FROM never returns NULL.

This operator is very useful in data comparison and change detection tasks.

Summary

IS DISTINCT FROM compares two values treating NULLs as real values.

It returns true if values differ or one is NULL and the other is not.

It returns false if values are equal or both NULL.