Wolfgang Walther <walt...@technowledgy.de> writes: > Peter Geoghegan: >> On Sun, Sep 25, 2022 at 8:55 AM Wolfgang Walther >> <walt...@technowledgy.de> wrote: >>> The attached patch adds a DO RETURN clause to be able to do this: >>> >>> INSERT INTO x (id) VALUES (1) >>> ON CONFLICT DO RETURN >>> RETURNING created_at; >>> >>> Much simpler. This will either insert or do nothing - but in both cases >>> return a row. >> How can you tell which it was, though? > > I guess I can't reliably. But isn't that the same in the ON UPDATE case? > > In the use cases I had so far, I didn't need to know. > >> I don't see why this statement should ever perform steps for any row >> that are equivalent to DO NOTHING processing -- it should at least >> lock each and every affected row, if only to conclusively determine >> that there really must be a conflict. >> In general ON CONFLICT DO UPDATE allows the user to add a WHERE clause >> to back out of updating a row based on an arbitrary predicate. DO >> NOTHING has no such WHERE clause. So DO NOTHING quite literally does >> nothing for any rows that had conflicts, unlike DO UPDATE, which will >> at the very least lock the row (with or without an explicit WHERE >> clause). >> The READ COMMITTED behavior for DO NOTHING is a bit iffy, even >> compared to DO UPDATE, but the advantages in bulk loading scenarios >> can be decisive. Or at least they were before we had MERGE. > > Agreed - it needs to lock the row. I don't think I fully understood what > "nothing" in DO NOTHING extended to. > > I guess I want DO RETURN to behave more like a DO SELECT, so with the > same semantics as selecting the row?
There was a patch for ON CONFLICT DO SELECT submitted a while back, but the author abandoned it. I hven't read either that patch that or yours, so I don't know how they compare, but you might want to have a look at it: https://commitfest.postgresql.org/16/1241/ > Best > > Wolfgang - ilmari
