onConflictDoUpdate Overwrites Good Values With Null

Conclusion

Drizzle’s .onConflictDoUpdate() writes whatever you pass to set straight over the existing row. If the source for your new data can return null for a few columns at a time, a naive set: { pm25, uvIndex, ... } will overwrite the last known-good reading with null the moment that happens. The fix is to wrap only the columns you want to preserve in coalesce(excluded.column, table.column).

Symptom

Air quality (PM2.5, photochemical oxidant, UV index) and heat index (WBGT) shown on our signage are fetched from external sources (Soramame-kun, Japan’s Ministry of the Environment) on a schedule and upserted into PostgreSQL. During certain hours, PM2.5 readings and WBGT peak values that had been displayed all day would flip to “—” on the widget.

No error was thrown. The fetch job itself succeeded every time.

Cause

The Soramame-kun / Ministry of the Environment CSV feeds sometimes fall back to an HTML page, or have no data for the matching monitoring station, and return null for every field during that window.

Before the fix, air-quality.ts passed whatever value it fetched straight into set on upsert.

.onConflictDoUpdate({
  target: [airQualityIndex.areaCode, airQualityIndex.source, airQualityIndex.forecastDate],
  set: {
    areaName: input.areaName ?? null,
    fetchedAt: input.fetchedAt ?? new Date(),
    pm25,
    pm25Band,
    oxidant,
    uvIndex,
    uvBand,
    raw: rawValue,
    updatedAt: new Date(),
    updatedBy: null,
  },
})

Because areaCode / source / forecastDate (i.e. today) form the conflict target, this onConflictDoUpdate runs every time the job re-fetches within the same day. On a run where the fetch came back null, pm25 and uvIndex were also null, and that null was passed to set, overwriting the existing row’s last known-good reading. heat-alerts.ts’s WBGT columns (wbgtMax / wbgtBand) shared the same structure and suffered the same bug.

Since forecastDate (today) is part of the conflict target, the overwrite is scoped to “within the same day” — a new day starts a new row, so the damage doesn’t carry over, but for the rest of that day the widget stays stuck on “—”.

Fix

The columns that needed to be preserved were switched to coalesce(excluded.column, table.column) via a sql template. excluded.column is the value from this INSERT (null if unavailable), table.column is the existing row’s value before the overwrite.

.onConflictDoUpdate({
  target: [airQualityIndex.areaCode, airQualityIndex.source, airQualityIndex.forecastDate],
  set: {
    areaName: input.areaName ?? null,
    fetchedAt: input.fetchedAt ?? new Date(),
    pm25: sql`coalesce(excluded.${sql.raw(airQualityIndex.pm25.name)}, ${airQualityIndex.pm25})`,
    pm25Band: sql`coalesce(excluded.${sql.raw(airQualityIndex.pm25Band.name)}, ${airQualityIndex.pm25Band})`,
    oxidant: sql`coalesce(excluded.${sql.raw(airQualityIndex.oxidant.name)}, ${airQualityIndex.oxidant})`,
    uvIndex: sql`coalesce(excluded.${sql.raw(airQualityIndex.uvIndex.name)}, ${airQualityIndex.uvIndex})`,
    uvBand: sql`coalesce(excluded.${sql.raw(airQualityIndex.uvBand.name)}, ${airQualityIndex.uvBand})`,
    raw: rawValue,
    updatedAt: new Date(),
    updatedBy: null,
  },
})

coalesce returns the first non-null value scanning left to right, so the existing pm25 (the last known-good reading) survives only when excluded.pm25 (the new data) is null. When the new fetch does have a value, that value wins and the row updates correctly as normal.

heat-alerts.ts was not changed column-by-column uniformly. alertLevel is still updated unconditionally.

set: {
  areaName: input.areaName ?? null,
  fetchedAt: input.fetchedAt ?? new Date(),
  alertLevel,
  wbgtMax: sql`coalesce(excluded.${sql.raw(heatAlerts.wbgtMax.name)}, ${heatAlerts.wbgtMax})`,
  wbgtBand: sql`coalesce(excluded.${sql.raw(heatAlerts.wbgtBand.name)}, ${heatAlerts.wbgtBand})`,
  raw: rawValue,
  updatedAt: new Date(),
  updatedBy: null,
}

alertLevel is a column where the value itself is legitimately supposed to change, such as dropping from severe to none in the evening. Wrapping it in COALESCE too would freeze a real “the alert level went down” update at its previous value. COALESCE should only protect “a measurement that can temporarily fall to null”; blanket-protecting “a state that can legitimately become null or a different value” introduces a different bug.

Since the schema (column definitions) itself wasn’t touched, this fix needed no migration.

Prevention

This is the same pattern already used for the temperature column in weather_forecasts. This fix carries that approach over to air quality and WBGT, and leaves the reasoning as a comment in the code:

// ★ For measured values, keep the existing value when the new one is null (same fix as
// temperature in weather-forecasts.ts). Soramame can return all-null for an hour when it
// falls back to HTML or has no matching station, and a non-COALESCE overwrite would wipe
// out the day's earlier PM2.5 reading, leaving signage stuck on "—" for the rest of the day.

When writing a new upsert that pulls in an external data source, the standard this comment leaves behind is: first check whether a fetch can succeed while still returning partial nulls, and if so, decide per column whether a plain assignment or COALESCE is the right call before passing it to set.