Add ON CONFLICT DO RETURN clause

From: Wolfgang Walther <walther(at)technowledgy(dot)de>
To: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Add ON CONFLICT DO RETURN clause
Date: 2022-09-25 15:55:05
Message-ID: ec5d2714-71cd-3e37-8698-7b9d9b09d87d@technowledgy.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Attachment Content-Type Size
v1-0001-Add-ON-CONFLICT-DO-RETURN-clause.patch text/plain 35.1 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Alvaro Herrera 2022-09-25 15:57:07 Re: tweak to a few index tests to hits ambuildempty() routine.
Previous Message Tom Lane 2022-09-25 15:51:59 Re: tweak to a few index tests to hits ambuildempty() routine.