Protocol

Detect changes involving NULL

'Not equal' can be unknown, not true.

About 1 min read

Before you use it

Use when
  • You compare source and target values before a data sync.

Keep this limit in mind

  • These comparison results do not define update intent. Check syntax and behavior in your own database; the example covers scalar values.

An idea to explore.

Try this idea.

Inspect the example, try the idea in your situation, then check your next move.

Keep reading

Why it matters

Ordinary SQL <> (not equal) returns UNKNOWN when either value is NULL, so a filter using it can miss a difference. For scalar values in PostgreSQL, IS DISTINCT FROM returns true when one value is NULL and the other is present; two NULLs return false. Use your database's supported comparison, then decide what the detected difference should do.

Steps

  1. Test five pairs: equal values, unequal values, NULL/NULL, value/NULL and NULL/value.
  2. Define whether an incoming NULL means clear the field, unknown or no update. Keep an empty string separate unless your data rules equate it with NULL.

An example

If the agreed rule says NULL clears a phone number, changing '123' to NULL needs an update. If NULL means no update, detecting the difference alone must not clear the number.

Check your result

With IS DISTINCT FROM, the five pairs return false, true, false, true and true respectively. The update also follows the agreed meaning of NULL.

Save and practise this idea

My learning

Read. Recall. Repeat.

Save this idea and return to it on any device.

Use email
Keep exploring

Connected ideas

You are readingDetect changes involving NULL

Evidence and sources
Supports

In PostgreSQL, ordinary comparisons involving NULL yield unknown; IS [NOT] DISTINCT FROM treats NULL as a comparable value.

Claim limit: Scalar comparisons in PostgreSQL 18. Composite keys and other engines require their own matching rules; this is not a query-planner guarantee. Source reading history: selected_passages; 2026-10-11; Section 9.2 Comparison Functions and Operators. Version 18 comparison passages rechecked on 2026-10-11: ordinary comparisons involving NULL and IS [NOT] DISTINCT FROM. Source reading is not production testing or proof of the drafted workflow. Agent release review: PostgreSQL 18 table expressions, NULL comparisons and aggregates, the UK Government Data Quality Framework and Microsoft Power Query profiling documentation were checked at the cited sections. The original examples and scope limits remain attached.

PostgreSQL 18: Comparison Functions and Operators · Section 9.2 Comparison Functions and Operators

All sources (1)
Edition and review

Version 0.1.0 · Published

Edition checked · 2026-11-12

Read this idea as JSON · Release details