Re: INSERT ON CONFLICT and RETURNING - Mailing list pgsql-hackers

On 22.08.2020 10:16, Konstantin Knizhnik wrote:
> Hi hackers,
>
> I am sorry for the question which may be already discussed multiple 
> times.
> But I have not found answer for it neither in internet neither in 
> pgsql-hackers archieve.
> UPSERT (INSERT ... IN CONFLICT...) clause was added to the Postgres a 
> long time ago.
> As far as I remember there was long discussions about its syntax and 
> functionality.
> But today I found that there is still no way to perform one of the 
> most frequently needed operation:
> locate record by key and return its autogenerated ID or insert new 
> record if key is absent.
>
> Something like this:
>
>   create table jsonb_schemas(id serial, schema bytea primary key);
>   create index on jsonb_schemas(id);
>   insert into jsonb_schemas (schema) values (?) on conflict(schema) do 
> nothing returning id;
>
> But it doesn't work because in case of conflict no value is returned.
> It is possible to do something like this:
>
>   with ins as (insert into jsonb_schemas (schema) values (obj_schema) 
> on conflict(schema) do nothing returning id) select coalesce((select 
> id from ins),(select id from jsonb_schemas where schema=obj_schema));
>
> but it requires extra lookup.
> Or perform update:
>
>   insert into jsonb_schemas (schema) values (?) on conflict(schema) do 
> update set schema=excluded.schema returning id;
>
> But it is even worse because we have to perform useless update and 
> produce new version.
>
> May be I missing something, but according to stackoverflow:
> https://stackoverflow.com/questions/34708509/how-to-use-returning-with-on-conflict-in-postgresql 
>
> there is no better solution.
>
> I wonder how it can happen that such popular use case ia not covered 
> by Postgresql UPSERT?
> Are there some principle problems with it?
> Why it is not possible to add one more on-conflict action: SELECT, 
> making it possible to return data when key is found?
>
> Thanks in advance,
> Konstantin

I'm sorry for been intrusive.
But can somebody familiar with Postgres upsert mechanism explain me why 
current implementation doesn't support very popular use case:
locate record by some unique key and and return its primary 
(autogenerated) key if found otherwise insert new tuple.
I have explained the possible workarounds of the problem above.
But all of them looks awful or inefficient.

What I am suggesting is just add ON CONFLICT DO SELECT clause:

insert into jsonb_schemas (schema) values ('one') on conflict(schema) do 
select returning id;

I attached small patch with prototype implementation of this construction.
It seems to be very trivial. What's wring with it?
Are there some fundamental problems which I do not understand?

Below is small illustration of how this patch is working:

postgres=# create table jsonb_schemas(id serial, schema bytea primary key);
CREATE TABLE
postgres=# create index on jsonb_schemas(id);
CREATE INDEX
postgres=# insert into jsonb_schemas (schema) values ('some') on 
conflict(schema) do nothing returning id;
  id
----
   1
(1 row)

INSERT 0 1
postgres=# insert into jsonb_schemas (schema) values ('some') on 
conflict(schema) do nothing returning id;
  id
----
(0 rows)

INSERT 0 0
postgres=# insert into jsonb_schemas (schema) values ('some') on 
conflict(schema) do select returning id;
  id
----
   1
(1 row)

INSERT 0 1


Thanks in advance,
Konstantin