ON CONFLICT DO UPDATE cannot assign an expression, so an upsert's updated_at must come from the app clock

[jryannel]

Where

mutate.go:115 — OnConflictUpdate(target []string, update ...string) takes column names only, and mutate.go:197-211 renders each one as col = EXCLUDED.col. There is no other spelling: OnConflictDoNothing and OnConflictUpdate are the whole ON CONFLICT surface.

What

An upsert cannot assign anything but the proposed row's own value. All three of these have no spelling:

ON CONFLICT (key) DO UPDATE SET updated_at = NOW()               -- database clock
ON CONFLICT (key) DO UPDATE SET count = table.count + 1          -- accumulate
ON CONFLICT (key) DO UPDATE SET x = COALESCE(EXCLUDED.x, table.x) -- keep on null

The updated_at = NOW() case is the common one, and the workaround is not neutral: computing the timestamp in Go moves its source from the database clock to the application clock, and forces the column into the INSERT list so it can be echoed back by EXCLUDED. For a table whose other timestamps come from Postgres, one column now disagrees with the rest under clock skew.

This is the gap docs/release-1.0.md:260 already records as Before 1.0, ranked by the multi-app port as "highest-value sqlb change surfaced by this port". Filing it so the roadmap entry has a tracking issue — there is none today, and a consumer reading the tracker to decide whether to wait for it finds nothing.

Both upserts in the multi-app port (core/secrets, core/llmcatalog) hit it. A second external evaluation (2026-08-01, studio-apps) independently names it that consumer's single highest-value upstream ask.

Fix

Admit an expression on the right-hand side of the assignment, keeping the bare-column form as the shorthand it already is. The Expr vocabulary exists; what is missing is a way to say which side of the conflict a column refers to, since EXCLUDED.x and <table>.x are both in scope inside DO UPDATE:

OnConflictUpdate([]string{"key"}, "name").
    Set("updated_at", sqlb.Now()).
    Set("count", sqlb.Excluded("count").Plus(sqlb.Current("count")))

Two constraints worth settling in the same change:

• The qualifier is not optional and should not be guessed. Excluded(col) and Current(col) (or whatever they end up called) are what make count = count + 1 unambiguous, and a bare F("count") inside DO UPDATE is ambiguous enough that it should probably be refused rather than defaulted.
• Whatever the column reference is, it must go through the same i.model.Column(name) == nil check the current renderer does at mutate.go:206, so a typo stays a compile-time-ish error rather than a 42703 at request time.

The parameterised form matters too — SET expires_at = $1 inside DO UPDATE — which means the assignment list has to participate in the same bind-parameter numbering as the VALUES list, not a separate one.

Surfaced by the multi-app adoption port and re-raised by an external evaluation on 2026-08-01.

[jryannel]

Fixed in ad1e164, on #99. All three of your examples have a spelling:

sqlb.InsertRows(&s).
    OnConflictUpdate([]string{"key"}, "payload").
    OnConflictSet("updated_at", sqlb.Now()).
    OnConflictSet("hits", sqlb.Add(sqlb.Current("hits"), sqlb.Val(1))).
    OnConflictSet("note", sqlb.Coalesce(sqlb.Excluded("note"), sqlb.Current("note")).Expr())

Both constraints you asked to settle in the same change, settled your way.

The qualifier is not guessed. Excluded and Current, and a bare F("count") inside an assignment is refused rather than defaulted — the expression is walked to find one, and the error offers both spellings by name. Raw is exempt, for the reason Raw is always exempt.

Names go through the model. Both sides: the assigned column and every Excluded/Current inside the expression, plus a refusal for assigning to a computed column. So a typo is an error from sqlb naming the column, not a 42703 at request time.

The parameterised form. Assignments render into the same compiler as the VALUES list, so their binds continue the same numbering — "payload" = $4, not a separate sequence that happens to line up. There is a test asserting the assignment's value is the last element of args and that the placeholder index matches.

Naming. OnConflictSet rather than the .Set(…) in your sketch — Insert.Set would read as setting a column on the insert, and this is only ever about the conflict branch. Easy to change if you disagree.

New vocabulary, kept to what the three cases actually need: Excluded, Current, Now, Val, Add, Sub. COALESCE needed nothing — Coalesce already existed and Selection.Expr() makes it usable in expression position, which is why the third example has no new function in it.

One thing that was not in the issue and would have been a bug: DO NOTHING was chosen on len(doUpdate) == 0, so an upsert carrying only an assignment — which is exactly the updated_at = now() case this opened with — would have compiled to a statement that writes nothing. The condition now accounts for assignments, with a test whose only job is to catch that.

Current qualifies to the target table, so it renders "secrets"."hits" rather than a bare "hits". Postgres resolves the bare form to the stored row anyway, but only because nothing else answers to that name — writing it out is what makes the statement say what it means.

docs/release-1.0.md moves this from Before 1.0 to Done, and docs/queries/mutations.md has the section.

Tests: conflict_set_test.go (all three shapes, the bind numbering, the ambiguity refusal, both name validations, the DO NOTHING trap) and pgtest/upsert_test.go — the accumulate case run three times, because a single conflict cannot distinguish Current from Excluded: on the first insert both hold the same value, and a wrong qualifier would return 1 every time instead of 1, 2, 3. That is a wrong number rather than an error, so it needs a database to catch. pgtest vets clean but I could not run it here — no Docker — so those two are unverified.