Thread: UPSERT/RETURNING -> ON CONFLICT SELECT?

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

On Wed, Jul 13, 2016 at 2:49 AM, Bjørnar Ness <bjornar.ness@gmail.com> wrote:
> 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;

I gather that the point of this pseudo SQL is to show how you might be
able to project and select the values not successfully inserted. Can't
you just pipeline together some CTEs instead?


--
Peter Geoghegan

Peter Geoghegan <pg@heroku.com> writes:
> On Wed, Jul 13, 2016 at 2:49 AM, Bjørnar Ness <bjornar.ness@gmail.com> wrote:
>> 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;

> I gather that the point of this pseudo SQL is to show how you might be
> able to project and select the values not successfully inserted. Can't
> you just pipeline together some CTEs instead?

What's "needed" seems a little ill-defined here, anyway.  Would the SELECT
be expected to return values from the failed-to-be-inserted row, or from
the existing conflicting row?  (Is there certain to be only one
conflicting row?  With exclusion constraints I'd think not.)
        regards, tom lane