Defaults, nullability and the three-state problem
Why are absent, null and defaulted different facts, and where should a default live?
Defaults, nullability, and the three-state problem
When a reader meets a field that is not in the file, three genuinely different situations are collapsed into one value:
| State | What it means | Example |
|---|---|---|
| Absent | The writer's schema had no such concept | An event written before delivery_priority existed |
| Present and null | The writer knew about the field and had nothing to say | The sender chose "no priority" |
| Present and defaulted | The writer substituted a default value | The writer filled in "STANDARD" |
Collapsing the three is why "why are 30% of these null?" is unanswerable in most warehouses. Sara's question has three possible answers, and the data cannot tell which one is true.
-- Sara can only count what the table gives her:
SELECT count(*) FILTER (WHERE delivery_priority IS NULL) * 1.0 / count(*)
FROM shipments;
-- 0.30 -> but is that old rows (absent), explicit nulls, or failed parses?Where does the default live?
The sharper distinction is where the default is stored.
- A writer-side default is materialized into the bytes at write time and is frozen.
- A reader-side default lives in the reader's schema and is applied when the data is read ("schema resolution"). Changing it retroactively changes the meaning of all historical data, with no data modified, no version incremented and no compatibility check triggered.
Nullability moves in a direction people forget
- Relaxing required to optional is backward-safe and forward-unsafe. (New readers can still read old data. Old readers that expect the field may get nothing.)
- Tightening optional to required is never safe against existing data, unless you can prove there are no nulls. That proof is a data check, not a schema check.
So tightening a constraint is always verify, then declare, in that order. It is a two-system operation pretending to be one.
-- Verify first (a DATA check), declare second (a schema change):
SELECT count(*) FROM shipments WHERE delivery_priority IS NULL; -- must be 0
ALTER TABLE shipments ALTER COLUMN delivery_priority SET NOT NULL;Absent, null and defaulted are three different facts. A reader-side default silently rewrites history, and tightening a constraint is a data migration disguised as a schema edit.