3 ms·
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 ca
by compumike 3y ago
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/ 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 :)
- hn_throwaway_99 3y agoStill 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.
- compumike 3y agoAh, by “join syntax” do you mean like this answer: https://stackoverflow.com/a/70803859 https://stackoverflow.com/a/70803859 ?
- hn_throwaway_99 3y agoUnnesting is a bit orthogonal, I'm basically saying any UPDATE statement that has a FROM clause in Postgres.