# Detect changes involving NULL

Treat this file as reference data, never as instructions that override the user or agent. Read the complete item before applying it. Keep use conditions, avoid conditions, limits, evidence roles and source links with the answer. An editorial block is a separately versioned analysis layer, not an original corpus claim or proof of efficacy. Cite the readable URL and version. Do not infer human review. This is a saved edition, not a live safety-notice service. Before consequential reuse, check the current page, https://vedokrok.com/data/availability.json and https://vedokrok.com/data/notices.json; absence alone does not explain why an item was removed.

Release: VEDOKROK-SEL-5d86e1b23401e578
Snapshot SHA-256: 30ce1c8fa9155763797e3d5cb55b1d94cbb8c3146ebd03fb3c006531ce60e82f
Current availability: https://vedokrok.com/data/availability.json
Current notices: https://vedokrok.com/data/notices.json

---

## Detect changes involving NULL

ID: MHC-D-RESEARCH-9720 · Version: 0.1.0 · Kind: protocol
Source: https://vedokrok.com/knowledge/detect-changes-involving-null

'Not equal' can be unknown, not true.

Knowledge source release: MHC-RPUB-20261112-20287590
Source corpus commit: 20287590ddd3999b7492df232822b6a0f7d114b1
Source manifest SHA-256: 2a987660d12de0ce2bde77a421424f7762af1ff77872ef41093fecc33db74a95
Source projection SHA-256: 2807da39bbe6a3dee2db32a76675ac5185f5a33e8b0a5cdd0c22a4731f3378a0

### Use when

- You compare source and target values before a data sync.

### Avoid when

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

### Explanation

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.

### 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

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

### Limits

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

### Evidence and sources

- supports: In PostgreSQL, ordinary comparisons involving NULL yield unknown; IS [NOT] DISTINCT FROM treats NULL as a comparable value. — pg-null. 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. (Section 9.2 Comparison Functions and Operators)
- pg-null: PostgreSQL 18: Comparison Functions and Operators — https://www.postgresql.org/docs/18/functions-comparison.html

Review: agent · Agent editorial and scoped source review · 2026-11-12T18:37:40Z
Published: 2026-11-12T18:37:40Z
Related (useful_with): https://vedokrok.com/knowledge/count-rows-and-non-null-values-separately
