Hacker News Reader

Comment by hn_throwaway_99 | Hacker News Reader

> we currently implement this with a transaction and a DELETE after the upsert because we want to do updates only, not unintentionally insert new rows.

Huh??? Why not just do an UPDATE then? After all, you seem to be saying "we want to do an upsert but without the insert part".

Edit: I'm also trying to understand the value of "WHEN NOT MATCHED THEN DO NOTHING". How is that any different that just leaving off the "WHEN NOT MATCHED" clause altogether?

View on HN

Batch update many many rows at once with entirely different values.

(Agree that the “WHEN NOT MATCHED DO NOTHING” is effectively the default behavior.)

Use case is the Outbound Prober processes for https://heiioncall.com/ which can do thousands of HTTP requests per second, then aggregate all those row updates into a single query (or single transaction, which in practice is a single query).

We can do these batched updates very, very fast using INSERT … ON CONFLICT. No need to re-parse a differently shaped query, only a single index rebuild. Smooth :)

Still not really understanding - you can do all of that with plain UPDATEs (using join syntax if you want to "batch update many many rows at once with entirely different values.") I would be hella confused as a developer if I saw an INSERT ... ON CONFLICT statement where you specifically wanted to prevent any inserts.

Ah, by “join syntax” do you mean like this answer: https://stackoverflow.com/a/70803859 ?

Unnesting is a bit orthogonal, I'm basically saying any UPDATE statement that has a FROM clause in Postgres.

Reply on news.ycombinator.com