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 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. -- Peter Geoghegan