UPSERT/RETURNING -> ON CONFLICT SELECT?

From: Bjørnar Ness <bjornar(dot)ness(at)gmail(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: UPSERT/RETURNING -> ON CONFLICT SELECT?
Date: 2016-07-13 09:49:23
Message-ID: CAJO99Tk8ZJ482RVGNn=147FmCZ=Tne2iOOJ18MU9bvEu5wXbZA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

The new upsert feature is a great addition, but in some cases is not
as usable as
I and seems lots of others would like it to be, take an example with
circular references:

create table foo (
id serial references bar(foo_id) on delete cascade,
i int
);

create table bar (
foo_id integer references foo(id) on delete cascade,
i int
);

A insert here would be:

with _foo as (
insert into foo(i) values(1) returning id
) insert into bar(foo_id,i)
select id,2 from _foo;

But with upsert/do nothing, this will not work as "needed".

Would it be possible to introduce a "ON CONFLICT SELECT" argument:

with _foo as (
insert into foo(i) values(1)
on conflict select returning id
) insert into bar(foo_id,i)
select id,2 from _foo;

--
Bj(/)rnar

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Amit Kapila 2016-07-13 11:27:48 Re: Simplifying the interface of UpdateMinRecoveryPoint
Previous Message Amit Langote 2016-07-13 09:22:36 Constraint merge and not valid status