What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
A blank cell in a CSV export exposed an unfinished backfill. The deeper problem was that accounts.locale had used NULL to mean three different things—and every reader had to guess which one it meant. The author’s post describes how one nullable field spread optional types and fallback branches across clients over five years. The lesson is broader than the anecdote: nullability is an interface contract, not just a storage choice.
How did one nullable field spread through the application?
In the post, accounts.locale began as nullable. As different parts of the system read it, each had to represent the possibility that no locale was present: Python used Optional[str], Go used *string, and TypeScript used string | null | undefined. Those examples are the author’s account of their clients, not independent measurements.
The field also accumulated fallback logic. A CSV export used SELECT *; while a backfill was incomplete, the export showed blank locale cells. That visible symptom pointed to the migration, but the longer-lived cost was that readers could not agree on what the absence meant.
Outdated 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 matchWindows 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 reinstallWhat did NULL mean in this column?
The post identifies three meanings hidden behind the same database 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 previously set preference.
These states can call for different behavior. A product might prompt for an unknown preference, skip locale-specific handling for an API-only account, and respect an explicitly cleared preference. Replacing every case with COALESCE(locale, 'en-US') gives a fallback value, but it also erases the distinction among those states. Whether that is acceptable is a product decision, not something the SQL expression can decide.
What SQL behavior makes NULL easy to misread?
Comparisons and NOT IN
PostgreSQL describes SQL as a three-valued logic system: “true, false, and null, which represents ‘unknown’.” A comparison involving NULL generally yields unknown rather than true or false. Use IS NULL or IS NOT NULL to test for absence; an ordinary WHERE condition retains rows only when the condition is true, so an unknown result is filtered out. See PostgreSQL 17’s logical operators documentation.
This is why a condition such as locale NOT IN ('en-US', 'fr-FR') does not select rows where locale is NULL. The comparison for those rows is unknown. If the intended result includes missing locales, make that explicit:
WHERE locale NOT IN ('en-US', 'fr-FR') OR locale IS NULLThat query groups NULL with the values outside the list. If unknown, not applicable, and cleared should behave differently, a single predicate cannot recover that information; the data model must preserve it.
Counts and aggregates
In PostgreSQL, count(*) counts input rows, while count(locale) counts rows where locale is non-null. The values therefore answer different questions: total accounts versus accounts with a recorded locale. Most built-in aggregates ignore NULL inputs, but check the documentation for the specific aggregate rather than assuming every function behaves alike. See PostgreSQL’s aggregate functions documentation.
Uniqueness
A regular PostgreSQL unique constraint treats NULL values as distinct by default. It can therefore allow multiple rows with NULL in a constrained column; the constraint does not mean “at most one missing value.” PostgreSQL 15 and later support NULLS NOT DISTINCT when NULLs should count as equal for uniqueness. Confirm the target database version and the intended rule before using it. See PostgreSQL’s unique-constraint documentation.
Should this column be nullable?
Choose the shape that expresses the real domain rule. Consider what absence means, how writers and readers use the field, which constraints can enforce valid combinations, and what migration and operational work the change requires.
Quick wins for a faster PC:
Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Repair Windows errors before they cause bigger problemsFix Now →Scan for outdated or missing drivers - takes under a minuteDriver Scan →| Data shape | Use it when | Trade-off to consider |
|---|---|---|
Required value with NOT NULL |
Every row should have a meaningful locale. | You need a truthful value for existing rows and a valid policy for future writes; a convenient placeholder can misrepresent the data. |
| Nullable column | Absence has one well-defined meaning and every consumer can handle it consistently. | Readers must use null-aware logic, and the meaning of NULL needs to be documented. |
| Non-null state field | Different absent states matter, such as unknown versus not applicable. | Define constraints so state and value cannot contradict one another. One lightweight pattern from the post is locale_state text NOT NULL DEFAULT 'unknown'. |
| Child table | The optional locale is better represented as a separate relationship: no row means absent, and one row holds a non-null value. | Reads and writes must account for the relationship; the post’s example is an account_locale table. |
No one shape is best for every system. The decision turns on whether absence is a single state or several, how the field is read and updated, and which rules the database should enforce.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.How do you migrate a nullable PostgreSQL column to NOT NULL?
Do not begin by replacing every NULL with an arbitrary default. First decide what each existing NULL represents and how future writes should behave. A safer migration is a sequence of semantic and operational changes, with commands adapted to the exact PostgreSQL version, schema, and workload.
Quick Recap
- Audit the field. Find writers, readers, exports, reports, and existing NULL rows. Establish whether the values represent one state or multiple states before choosing a mapping.
- Define the invariant. Decide which value or state each row must have, and update the application’s write path so newly created or changed rows satisfy the intended rule.
- Backfill deliberately. Map existing values according to their meaning. Plan batch size, transaction duration, and monitoring for the workload; do not assume a large update is operationally harmless.
- Add and validate a check constraint if staged validation fits. PostgreSQL can add a constraint with
NOT VALID, then check pre-existing rows later withVALIDATE CONSTRAINT. Adding it this way does not scan the table at the initial command. Validation scans existing rows and acquires a lock, while allowing concurrent updates. This is not a promise of zero locking or zero impact. See PostgreSQL’s ALTER TABLE documentation. - Enforce NOT NULL. After the data satisfies the invariant, apply
SET NOT NULLand verify the behavior for the target PostgreSQL version. Whether a valid check constraint can help avoid a scan, and the operational impact of the change, depend on version and circumstances. - Remove obsolete branches after deployment. Once the constraint is in place and the updated application is running, delete fallback paths that no longer represent a valid state. Check exports and reports too, not only the main application.
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.
