Use COALESCE for display fallbacks

By Batu · English technical notes

Also published in our primary archive.

COALESCE is a useful display projection when a missing database value needs a readable label. SQLite defines it as returning the first non-NULL argument (or NULL if every argument is NULL), so COALESCE(nickname, '(not supplied)') supplies text only in the query result. SQLite’s function reference documents that behavior.

This example deliberately stores two different states: Ada has no nickname (NULL), while Bo has an explicitly empty nickname (''). The raw query returns Python None for Ada and an empty string for Bo. The display query substitutes (not supplied) for Ada only; it does not update the table. Its assertions verify the precise rows returned by these two queries, and the single output line makes the distinction visible.

Keep this pattern scoped to presentation unless the application’s domain explicitly defines the fallback as the same meaning as NULL. For example, using a placeholder in grouping, filtering, or exports can merge missing values with genuinely stored text. Conversely, a domain-defined default may make COALESCE appropriate outside display work; the query alone cannot decide that semantic question. If a fallback comes from outside the program, pass it as a bound parameter instead of constructing SQL text. Python’s sqlite3 documentation describes qmark placeholders and execute(). This uses only long-standing APIs; Python 3.6+ is required for the f-string used in output. AI assistance disclosure: this article was drafted with AI assistance.

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE profile (name TEXT, nickname TEXT)")
con.executemany(
    "INSERT INTO profile VALUES (?, ?)",
    [("Ada", None), ("Bo", "")],
)

stored = con.execute(
    "SELECT name, nickname FROM profile ORDER BY name"
).fetchall()
display = con.execute(
    "SELECT name, COALESCE(nickname, '(not supplied)') "
    "FROM profile ORDER BY name"
).fetchall()

assert stored == [("Ada", None), ("Bo", "")]
assert display == [("Ada", "(not supplied)"), ("Bo", "")]
print(f"stored={stored}; display={display}")
con.close()

stored=[('Ada', None), ('Bo', '')]; display=[('Ada', '(not supplied)'), ('Bo', '')]