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
