Postgres Evaluates the Branch Your CASE Never Takes

I was adding an optional date to an insert. The caller either sends a day the thing happened on, or sends nothing and means "now." One statement, one guard:

INSERT INTO planting_events (planting_id, kind, notes, occurred_at)
VALUES (%s, 'note', %s,
        CASE WHEN %s = '' THEN now()
             ELSE (%s::date + now()::time) AT TIME ZONE %s END)

Send a date, it works. Send an empty string, it should take the now() branch. Instead:

psycopg2.errors.InvalidDatetimeFormat: invalid input syntax for type date: ""
LINE 4:      ELSE (''::date + now()::time) AT TIM...

That's the error from the arm the WHEN just ruled out.

Why

CASE is documented as short-circuiting, and it does — at execution time. But constant folding runs earlier, during planning, and it doesn't care which arm the guard would have chosen. Both parameters were bound to the same empty string, so by the time the planner looked at the ELSE, it saw the literal ''::date: a cast of a constant to a type, which it can evaluate once up front instead of per row. So it did. And '' is not a date.

The Postgres docs actually warn about this, in a note most people (me) skim past — the short-circuit guarantee doesn't extend to subexpressions that get folded before the plan exists. It's the same reason a CASE WHEN x <> 0 THEN y/x ELSE 0 END guard is safe (division is per-row) while a constant cast in the dead arm is not.

The tell is right there in the error: the LINE 4 echo shows ''::date, not $4::date. The parameter was already gone. If the planner had waited, it would still have been a placeholder.

The fix

Stop asking SQL to make the choice. Branch in the application, and send two different statements:

if on:
    db.q("""INSERT INTO planting_events (planting_id, kind, notes, occurred_at)
            VALUES (%s, 'note', %s, (%s::date + now()::time) AT TIME ZONE %s)""",
         (pid, body, on, TZ))
else:
    db.q("""INSERT INTO planting_events (planting_id, kind, notes, occurred_at)
            VALUES (%s, 'note', %s, now())""",
         (pid, body))

Uglier on the page, four lines longer, and it has the pleasant property of working.

You can also keep it in SQL by making the empty case unreachable before the cast — NULLIF(%s, '')::date folds to NULL::date, which is legal, and then COALESCE(..., now()) picks up the slack. That's the clever version. I took the boring one, because the next person to read that insert shouldn't have to reconstruct why a NULLIF is load-bearing.

What I'd do differently

I wrote the guard because I wanted one statement instead of two, which is a preference about how the code looks, not about what it does. The bug lived in the gap between those. Two plain statements would have shipped correct on the first deploy and cost me nothing I actually care about.

More generally: the moment a SQL expression starts doing control flow, that's the signal to move the control flow up a layer. The database is very good at sets and very indifferent to your desire for a tidy one-liner.