Add ON CONFLICT DO RETURN clause
When using ON CONFLICT DO NOTHING together with RETURNING, the
conflicted rows are not returned. Sometimes, this would be useful
though, for example when generated columns or default values are in play:
CREATE TABLE x (
id INT PRIMARY KEY,
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMEMSTAMP
);
To get the created_at timestamp for a certain id **and** at the same
time create this id in case it does not exist, yet, I can currently do:
INSERT INTO x (id) VALUES (1)
ON CONFLICT DO UPDATE
SET id=EXCLUDED.id
RETURNING created_at;
However that will result in a useless UPDATE of the row.
I could probably add a trigger to prevent the UPDATE in that case. Or I
could do something in a CTE. Or in multiple statements in plpgsql - this
is what I currently do in application code.
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.
Thoughts?
Best
Wolfgang
Attachments:
v1-0001-Add-ON-CONFLICT-DO-RETURN-clause.patchtext/plain; charset=UTF-8; name=v1-0001-Add-ON-CONFLICT-DO-RETURN-clause.patchDownload+587-15
On Sun, Sep 25, 2022 at 8:55 AM Wolfgang Walther
<walther@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
Peter Geoghegan:
On Sun, Sep 25, 2022 at 8:55 AM Wolfgang Walther
<walther@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?
Best
Wolfgang
Wolfgang Walther <walther@technowledgy.de> writes:
Peter Geoghegan:
On Sun, Sep 25, 2022 at 8:55 AM Wolfgang Walther
<walther@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