ON CONFLICT DO NOTHING RETURNING
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
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general