Recommended Free Tools
A CSV export exposed the problem: unfinished backfill work had left blank cells in accounts.locale. In the system described by the post, the deeper issue was that NULL had come to mean three different things—unknown, not applicable, and empty—and each reader had to guess which one it was.
Why one nullable field spread through every client
The post describes a nullable accounts.locale field whose optionality propagated into client code: Python used Optional[str], Go used *string, and TypeScript used string | null | undefined. Consumers also accumulated fallback logic. These are examples from the author’s system, not independent measurements, but they illustrate the interface cost: every reader needs a rule for absence.
As an Amazon Associate I earn from qualifying purchases.
The CSV export used SELECT *, so blank locale cells made the incomplete backfill visible. The export was a symptom; the harder problem was that no single, stable meaning of absence existed for consumers to follow.
Do these 3 things before closing this tab:
1Clear out junk files and repair common Windows errors2Fix the driver behind crashes, sound loss and screen glitches3Repair Windows errors before they cause bigger problemsWhat does NULL mean in this column?
The post identifies three meanings that had been folded into one value:
#1 Best Overall
- Unknown: the user had not been asked for a locale.
- Not applicable: the account was API-only.
- Empty: the user had cleared a preference.
These states can imply different application behavior. A default locale might be appropriate when a value is unknown, but not when the user deliberately cleared a preference. An API-only account may need no locale at all. Replacing every NULL with 'en-US' through COALESCE(locale, 'en-US') hides those distinctions rather than resolving them.
A nullable column is a sound choice when absence has one well-defined meaning and writers and readers apply it consistently. If absence represents multiple product states, record those states explicitly or model the optional fact separately.
Rank #2
How NULL changes SQL results
Comparisons and NOT IN
PostgreSQL uses three-valued logic: an expression can evaluate to true, false, or unknown. Its PostgreSQL 17 documentation says, “SQL uses a three-valued logic system with true, false, and null, which represents ‘unknown’.” (PostgreSQL 17: Logical Operators)
That matters because a comparison with NULL generally evaluates to unknown, not true or false. A WHERE clause keeps rows only when its condition is true, so equality or inequality filters do not retain rows whose comparison is unknown. Test nullness explicitly with IS NULL or IS NOT NULL.
Rank #3
The same logic explains a common NOT IN surprise. If the list on the right contains NULL, a non-matching value may produce unknown instead of true; the row is then filtered out. If nulls are possible in either the column or the list, define the intended behavior explicitly rather than assuming NOT IN means “not equal to any listed value.” (PostgreSQL 17: Comparison Functions and Operators)
Counts and aggregates
PostgreSQL distinguishes row counts from non-null value counts: count(*) counts input rows, while count(locale) counts rows where locale is not null. Most built-in aggregates ignore null inputs, but aggregate behavior should be checked for the specific function. A report must say whether it means all accounts or only accounts with a recorded locale. (PostgreSQL 17: Aggregate Functions)
Rank #4
Uniqueness
A regular PostgreSQL unique constraint treats nulls as distinct by default, so multiple rows can have NULL in a unique column. If nulls should count as equal for this rule, PostgreSQL 15 and later provide NULLS NOT DISTINCT; verify the target server version and intended semantics before relying on it. (PostgreSQL 17: Constraints)
Windows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallOutdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchChoose a data shape that matches the meaning
| Shape | Use it when | What to account for |
|---|---|---|
Required value with NOT NULL |
Every row should have a meaningful value. | Choose a truthful default or a defensible backfill mapping; a convenient placeholder can change the meaning. |
| Nullable column | Absence is one well-defined state. | Document that meaning and make readers handle it consistently, using explicit null predicates where needed. |
| Non-null state field | Several absent states matter, such as unknown versus not applicable. | For example, locale_state text NOT NULL DEFAULT 'unknown' can represent the state separately. Add constraints so state and value combinations remain valid. |
| Child table | The optional fact is better represented as a separate relationship. | For example, zero account_locale rows can mean absent; one row holds a non-null value. Consider the query and update patterns that follow from the relationship. |
Compare candidate designs on semantic clarity, integrity constraints, query shape, consumer complexity, migration risk, and operational cost. The right choice depends on how many meanings absence has and how the data is read and updated.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Migrate to NOT NULL without guessing at the data
For PostgreSQL, treat this as a semantic and operational migration, not just a column alteration. First decide what each existing null means and how future writes should behave. Then make the data satisfy that rule before enforcing it.
Quick Recap
- Audit the contract. Find writers, readers, exports, filters, aggregates, and constraints involving the column. Count existing nulls and determine whether they correspond to distinct states.
- Choose the target meaning. Decide whether every row needs a value, whether one consistent nullable state is valid, or whether a state field or child table is required.
- Deploy compatible writes and readers. Make new writes follow the chosen semantics. Update consumers so they no longer infer different meanings from the same null.
- Backfill deliberately. Map existing rows based on evidence about their meaning. Do not assign a default merely to make nulls disappear. Run the backfill in a way suited to table size, write volume, and workload.
- Add and validate a check constraint when staging helps. PostgreSQL supports
ADD CONSTRAINT ... CHECK (locale IS NOT NULL) NOT VALID, followed byVALIDATE CONSTRAINT. Adding aNOT VALIDconstraint avoids scanning existing rows at that command; validation checks them later and allows concurrent updates, but it still scans the table and acquires a lock. Assess the exact server version and workload. (PostgreSQL 17: ALTER TABLE) - Enforce the column rule. After the data is clean, apply
SET NOT NULLif the chosen design requires a value. Its operational behavior depends on PostgreSQL version and whether a valid check constraint proves the condition; consult the documentation for the target version rather than assuming the change is lock-free. - Remove obsolete branches after deployment. Once the constraint is enforced and all relevant consumers have adopted the contract, delete fallback paths that only existed to handle the old ambiguity.
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
