Re: Feature suggestion: ON CONFLICT DO NOTHING RETURNING which returns existing row data

From: Tom Dunstan <pgsql(at)tomd(dot)cc>
To: Pantelis Theodosiou <ypercube(at)gmail(dot)com>
Cc: "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Feature suggestion: ON CONFLICT DO NOTHING RETURNING which returns existing row data
Date: 2016-10-06 07:49:39
Message-ID: 25247983-2BB2-4B2F-86E7-C9F313278F81@tomd.cc
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers


> On 5 Oct 2016, at 8:11 PM, Pantelis Theodosiou <ypercube(at)gmail(dot)com> wrote:
>
> This can be solved by chaining modifying CTEs.
>
> Something like this (not tested) that can work with multiple rows inserted:

Thanks for the suggestion, but it was actually slower than our current implementation, I believe due to always looking up t1’s id in that join rather than only doing it when we didn’t get an id back from the insert. My hope with this feature suggestion / request was that we wouldn’t have to do that subsequent lookup at all, as pg would just give it back to us.

Maybe it would be a win if we were inserting multiple rows, but this code is actually in a trigger on a dummy table that we COPY data in to - thus it can’t be rewritten as a rule or a multi-row insert like that.

Thanks

Tom

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Masahiko Sawada 2016-10-06 08:04:07 Re: Transactions involving multiple postgres foreign servers
Previous Message Amit Langote 2016-10-06 07:14:04 Re: Declarative partitioning - another take