| 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: | Whole Thread | Raw Message | 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
| 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 |