There's No Nothing in a CSV File
An empty field isn’t NULL. And that difference can quietly break your logic.
The other day, I was preparing a CSV for a database import.
The schema had two name fields — name_local and name_english.
Downstream systems were supposed to use the local name if available, otherwise fall back to the English one.
Simple enough — until I saw that sometimes this fallback logic didn’t work.
The problem was quickly discovered: Missing name_local values weren't set to NULL; they were empty strings. And an SQL query like COALESCE(name_local, name_english) returns this empty string — because an empty string isn't NULL in SQL logic.
That’s not a bug. It’s ambiguity.
CSV has no concept of types. Everything is a string.
An empty field isn’t NULL, it’s an empty string.
And the interpretation of that emptiness depends entirely on the program reading it.
There’s no perfect fix — only conventions.
Depending on your use case, an empty string should always mean an empty string. Or always mean NULL.
Either way, be sure to document your convention. Everyone touching your CSV file — or providing their own — has to know what an empty string represents.
When deciding on your convention, pay attention to your setup. Some databases know about the ambiguity and offer solutions. PostgreSQL’s COPY command, for example, supports options like FORCE_NULL, letting you explicitly define how empties should be interpreted.
That's great. Just make sure your convention is compatible with your database settings.
If you want to avoid messing with conventions that rely on documentation nobody really reads (or remembers when needed), move to a typed format like JSON or even JSONL — where an empty string always means an empty string, as these formats also offer a “real” NULL.
Just to be clear: CSV isn’t failing here, though. It’s doing what it was designed to do: store text (and numbers disguised as text). If you have the need to store more than that, use a format that's a better fit for your needs.
Thanks for reading,
Stefan
