# Null, Blank, and Zero in Business Data: Keep Missing Values From Becoming False Facts

<a id="main-content"></a>

Operations &amp; Systems

October 28, 2026  ·  8 min read  ·  Victor Valentine Romo

On this page

1. [Define the Measurement Before Interpreting Its Absence](<#define-the-measurement-before-interpreting-its-absence>)
2. [Separate Storage From Business Meaning](<#separate-storage-from-business-meaning>)
3. [Use IS NULL to Find the Missing Measurements](<#use-is-null-to-find-the-missing-measurements>)
4. [Write the Denominator Beside the Average](<#write-the-denominator-beside-the-average>)
5. [Calculate What a Zero Replacement Would Assert](<#calculate-what-a-zero-replacement-would-assert>)
6. [Copy the Missing-Value Decision Table](<#copy-the-missing-value-decision-table>)
7. [Preserve the Explanation Through the Next Report](<#preserve-the-explanation-through-the-next-report>)
8. [Sources](<#sources>)

A duration field is empty. Someone replaces it with zero so the report looks complete.

The average falls. No new measurement arrived.

Keep the missing value visible until someone can explain it. A measured zero, an unavailable measurement, and an inapplicable question need different labels and different follow-up work.

The proposed worksheet below preserves those distinctions through a fictional service report. All identifiers, durations, and reasons are invented. The SQL examples target PostgreSQL 18 with standard null comparison behavior. They describe read-only calculations over synthetic records.

<a id="define-the-measurement-before-interpreting-its-absence"></a>

## Define the Measurement Before Interpreting Its Absence

For this example, duration means recorded hands-on service minutes for one completed request. The fictional measurement process accepts whole minutes, including zero. A zero is valid only when the source explicitly records it as measured.

The example does not measure waiting time, customer satisfaction, or [elapsed time](</articles/excel-durations-over-24-hours/>) between opening and closing. A request can be complete while its duration is unavailable. Completion and measurement status belong in separate fields.

Assume the team retained five completed requests for one reporting period. No request is removed because its duration is missing. Every request has a unique identifier.

| Request ID | Duration, minutes | Measurement state | Known reason |
| --- | --- | --- | --- |
| S01 | 0 | Measured | Source explicitly records zero |
| S02 | 10 | Measured | Completed measurement |
| S03 | 20 | Measured | Completed measurement |
| S04 | NULL | Missing | Measurement was not captured |
| S05 | NULL | Inapplicable | Request required no hands-on service; owner excludes it from duration measurement |

The reason for S05 is an explicit assumption of this fictional contract. Another operation might define such a request as measured zero. Ask the metric owner which definition applies before transforming the value.

S04 has a known reason for the gap. Its duration remains unknown. Knowing why a measurement is absent does not reveal what the measurement would have been.

Keep the raw duration and reason separately. Do not replace the numeric field with a sentence to make the export readable. Use a display label beside the numeric value when presenting the report.

<a id="separate-storage-from-business-meaning"></a>

## Separate Storage From Business Meaning

In PostgreSQL 18, SQL `NULL` represents a missing value state. An empty text value, `''`, is a text value. The literal text `'null'` also has characters. PostgreSQL's [comparison reference](<https://www.postgresql.org/docs/18/functions-comparison.html>) explicitly shows that `'null' IS NOT NULL` is true.

A [spreadsheet](</articles/spreadsheet-formula-audit-business/>) cell that looks blank does not establish which state an export will deliver. Treat the visual appearance as an observation. Inspect the exported value and the agreed import rule before drawing a conclusion.

For the proposed handoff, reserve the duration column for numbers or SQL null after parsing. Keep the original incoming representation in controlled source evidence. A text marker needs an explicit mapping before it enters the typed duration field.

| Observed representation | What it establishes | Proposed handling |
| --- | --- | --- |
| Numeric `0` with measured status | Source asserts measured zero | Preserve zero and status |
| SQL `NULL` | Numeric value is absent | Read the separate reason field |
| Empty text `''` | Text field contains no characters | Resolve the column-specific import rule |
| Literal text `'null'` | Text field contains four characters | Map only if the contract reserves this marker |
| Cell appears blank | Display contains no visible value | Inspect source, export, and parsing behavior |

Do not let [one global cleanup rule](</articles/crm-data-quality-automation/>) decide all five cases. An empty optional note can be acceptable while an empty measurement requires investigation. Column meaning controls the decision.

A [CSV data contract](</articles/csv-data-contract-business-handoffs/>) provides a place for these exchange rules. Record the marker, allowed field, destination type, reason mapping, and rejection condition. Keep this article's reporting decisions separate from the mechanics of importing a file.

<a id="use-is-null-to-find-the-missing-measurements"></a>

## Use IS NULL to Find the Missing Measurements

Under standard PostgreSQL 18 behavior, ordinary comparisons involving null yield unknown. Use `IS NULL` or `IS NOT NULL` to test scalar missing values, as documented in the [comparison predicates](<https://www.postgresql.org/docs/18/functions-comparison.html#FUNCTIONS-COMPARISON-PRED-TABLE>).

This query assumes a fictional `service_duration` table containing the five rows above. It returns S04 and S05:

```sql
SELECT request_id, measurement_state, known_reason
FROM service_duration
WHERE duration_minutes IS NULL
ORDER BY request_id;
```

Do not use `duration_minutes = NULL` to retrieve those records. The comparison does not establish a true match under the stated behavior.

Also review the opposite filter. `duration_minutes <> 0` does not mean every record except measured zero. The missing values produce unknown comparisons. For the synthetic records, a filter retaining true comparisons returns only S02 and S03.

If a review needs both nonzero values and missing values, state that scope explicitly:

```sql
SELECT request_id, duration_minutes, measurement_state
FROM service_duration
WHERE duration_minutes <> 0
   OR duration_minutes IS NULL
ORDER BY request_id;
```

The expected identifiers are S02, S03, S04, and S05. The query defines a review population. It does not make missing durations numerically comparable with ten or twenty minutes.

Keep the measurement-state field in the result. A reviewer seeing only an empty duration cannot distinguish an uncaptured measurement from the documented inapplicable case.

<a id="write-the-denominator-beside-the-average"></a>

## Write the Denominator Beside the Average

PostgreSQL 18 documents `COUNT(*)` as counting input rows, `COUNT(expression)` as counting non-null inputs, and `AVG(expression)` as averaging non-null values. See its [aggregate-function reference](<https://www.postgresql.org/docs/18/functions-aggregate.html>).

Run the three calculations over the same fictional population:

```sql
SELECT COUNT(*) AS completed_requests,
       COUNT(duration_minutes) AS measured_requests,
       AVG(duration_minutes) AS mean_measured_minutes
FROM service_duration;
```

The results are five completed requests, three measured requests, and ten mean measured minutes. The arithmetic is zero plus ten plus twenty, divided by three. The measured zero participates in the [denominator](</articles/two-way-tables-business-reporting/>).

Label the result “Mean recorded duration among three measured requests: ten minutes.” Add “Five completed requests; one missing measurement; one inapplicable request.” These labels describe the synthetic population without claiming that the measured subset represents every request.

If the owner defines [measurement coverage](</articles/data-quality-dimensions-business-reports/>) among applicable requests, its denominator is four: S01 through S04. Three have measurements. Coverage is three divided by four, or seventy-five percent. This is a proposed coverage definition, not a property of the aggregate function.

Keep the duration denominator and coverage denominator visible. The mean uses three measurements. Coverage uses four applicable requests. The overall population contains five completed requests. Each count has a separate meaning.

Record those definitions in the [business metric dictionary](</articles/business-metric-dictionary-template/>). A report label such as “average service time” leaves too much room for an unrecorded change in population.

<a id="calculate-what-a-zero-replacement-would-assert"></a>

## Calculate What a Zero Replacement Would Assert

For a counterexample, imagine replacing both missing durations with zero. The resulting sequence is zero, ten, twenty, zero, zero.

Its sum remains thirty. Its denominator becomes five. [Its mean becomes six minutes](</articles/mean-median-business-reports/>).

Nothing in the fictional source supports that replacement. S04 has no measurement. S05 is outside the chosen measurement definition. The transformation silently asserts a value for both records.

Even replacing only S04 changes the meaning. Excluding inapplicable S05, the sequence becomes zero, ten, twenty, zero. Thirty divided by four gives seven and a half minutes. That number describes an invented zero alongside the three recorded measurements.

These calculations demonstrate the consequence of the proposed replacement. They are not estimates of the missing duration or evidence that one report is closer to an unknown true average.

Avoid filling the gap with the current average for the same reason. This worksheet authorizes no imputation. If a separate analysis requires estimated values, it needs its own method, assumptions, approval, and output labels.

Preserve the measured result and its limits. A partial measurement set can support a clearly bounded statement. Hiding the missingness removes the reader's ability to assess that boundary.

<a id="copy-the-missing-value-decision-table"></a>

## Copy the Missing-Value Decision Table

Use one row per distinct source state and known reason. Add the metric identifier, definition version, reporting period, [source reference](</articles/data-lineage-small-business-reports/>), and responsible owner above the table.

| Source state | Known reason | Permitted transformation in this example | Output label | Unresolved question |
| --- | --- | --- | --- | --- |
| Numeric zero; measured | Source explicitly recorded zero | Preserve numeric zero | Measured: 0 minutes | Does the source measurement definition match ours? |
| Numeric positive value; measured | Measurement completed | Preserve value and unit | Measured: \[value\] minutes | Was the agreed reporting cutoff used? |
| Null; missing | Measurement not captured | Preserve null and reason | Missing measurement | Can the source owner supply a recorded value? |
| Null; inapplicable | Owner excludes this case from duration measurement | Preserve null; exclude from applicable coverage denominator | Not applicable under this definition | Is the exclusion still the approved rule? |
| Null; reason absent | Unknown | Preserve null; flag unresolved | Missing value; reason unknown | Who can establish the reason? |
| Empty text or reserved marker | Depends on the exchange contract | Apply only the documented field mapping | State after mapping, with evidence | Does the contract define this representation? |

The table proposes decisions for this example. It cannot establish a missing reason from the storage representation. Assign unresolved rows to the person who controls the source or measurement definition.

Keep rejection separate from missingness. A duration containing an unexpected word might fail the handoff contract. Silently converting it to null would hide the invalid input. Retain the exception and [ask for a corrected delivery](</articles/file-delivery-manifest-business-handoffs/>).

<a id="preserve-the-explanation-through-the-next-report"></a>

## Preserve the Explanation Through the Next Report

Before handing the result to [reporting automation](</articles/reporting-automation-b2b-services/>), ask the next reader to reproduce the three population counts. Have them identify which records enter the mean and why.

Retain the query, definition version, input reference, and missing-value [decision table](</articles/business-decision-table-gaps-overlaps/>) together. A future report refresh needs the same interpretation rules even when the rows change.

If S04 later receives a recorded duration, update its measurement state from source evidence. Keep a note explaining the revised input and [which published period changed](</articles/late-arriving-data-report-revisions/>). Do not present the revision as a newly measured zero unless zero is what the source recorded.

If the owner changes the treatment of S05, version the metric definition. Recalculate comparisons under an agreed rule. A label that stays the same while its denominator changes conceals a reporting decision.

When [no measurements exist for a period](</articles/missing-intervals-business-time-series/>), display an unavailable measured average with its population counts. A proposed report rule should make that state readable without inventing a numerical result.

Leave the unknown visible.

<a id="sources"></a>

## Sources

- [PostgreSQL 18: Comparison Functions and Operators](<https://www.postgresql.org/docs/18/functions-comparison.html>). Standard scalar null comparisons, `IS NULL`, and the distinction between SQL null and literal text. Checked 2026.10.28.
- [PostgreSQL 18: Aggregate Functions](<https://www.postgresql.org/docs/18/functions-aggregate.html>). `COUNT(*)`, `COUNT(expression)`, and `AVG(expression)` behavior. Checked 2026.10.28. Population definitions and arithmetic examples are editorial [proposals](</articles/proposal-templates-b2b-services/>).
