Postgres Pro
Downloads
Home   >   mailing lists

Thread: ON CONFLICT DO NOTHING RETURNING

ON CONFLICT DO NOTHING RETURNING

From
Peter Devoy
Date:
Hi all

Is it possible to have ON CONFLICT DO NOTHING RETURNING or must I use DO UPDATE?

E.g. if the 'name' column of my 'animals' table is unique it seems a little silly having to do an arbitrary update to get an ID from the row:

INSERT INTO animals (name) VALUES ('dog') ON CONFLICT (name) DO UPDATE SET name='dog' RETURNING animal_id;

Is there a reason DO NOTHING was not developed for use with RETURNING?

Either way, upsert is great, I am glad we have it now.

Kind regards


Peter Devoy

Re: ON CONFLICT DO NOTHING RETURNING

From
Peter Geoghegan
Date:
On Mon, Mar 14, 2016 at 12:28 PM, Peter Devoy <peter@3xe.co.uk> wrote:
> Is there a reason DO NOTHING was not developed for use with RETURNING?

I don't know what you mean. It should work fine with RETURNING.

--
Peter Geoghegan

Re: ON CONFLICT DO NOTHING RETURNING

From
Jeff Janes
Date:
On Mon, Mar 14, 2016 at 1:20 PM, Peter Geoghegan <pg@bowt.ie> wrote:
> On Mon, Mar 14, 2016 at 12:28 PM, Peter Devoy <peter@3xe.co.uk> wrote:
>> Is there a reason DO NOTHING was not developed for use with RETURNING?
>
> I don't know what you mean. It should work fine with RETURNING.


He wants to retrieve a value from the conflicting row.  Now getting
the value that caused the conflict should be easy, because you
provided it in the first place.   But he wants a value from a
different column of the conflicting row than the column(s) on which
there is conflict.  DO NOTHING RETURNING returns no rows.  Which is
reasonable, because nothing was inserted.  But it isn't what he wants.

I think the dummy update is his best bet, but it does seem like there
should be a better way.  Maybe ON CONFLICT DO SELECT where the select
operates over the target row.

Cheers,

Jeff

Re: ON CONFLICT DO NOTHING RETURNING

From
Peter Geoghegan
Date:
On Fri, Mar 18, 2016 at 9:14 AM, Jeff Janes <jeff.janes@gmail.com> wrote:
> He wants to retrieve a value from the conflicting row.  Now getting
> the value that caused the conflict should be easy, because you
> provided it in the first place.   But he wants a value from a
> different column of the conflicting row than the column(s) on which
> there is conflict.  DO NOTHING RETURNING returns no rows.  Which is
> reasonable, because nothing was inserted.  But it isn't what he wants.

I see.

> I think the dummy update is his best bet, but it does seem like there
> should be a better way.  Maybe ON CONFLICT DO SELECT where the select
> operates over the target row.

Seems reasonable.


--
Peter Geoghegan