Re: INSERT ON CONFLICT and RETURNING

On 24.08.2020 13:37, Geoff Winkless wrote:
> On Sat, 22 Aug 2020 at 08:16, Konstantin Knizhnik
> <k(dot)knizhnik(at)postgrespro(dot)ru> wrote:
>> 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.
> But if
>
> INSERT INTO jsonb_schemas (schema) VALUES (obj_schema)
> ON CONFLICT (schema) DO NOTHING RETURNING id
>
> were to work then that would _also_ require a second lookup, since
> "id" is not part of the conflict key that will be used to perform the
> existence test, so the only difference is it's hidden by the syntax.
>
> Geoff
Sorry, I didn't quite understand it.
If we are doing such query:

INSERT INTO jsonb_schemas (schema) VALUES (obj_schema)
ON CONFLICT (schema) DO UPDATE schema=jsonb_schemas.schema RETURNING id

Then as far as I understand no extra lookup is used to return ID:

 Insert on jsonb_schemas  (cost=0.00..0.01 rows=1 width=36) (actual
time=0.035..0.036 rows=0 loops=1)
   Conflict Resolution: UPDATE
   Conflict Arbiter Indexes:jsonb_schemas_schema_key
   Conflict Filter: false
   Rows Removed by Conflict Filter: 1
   Tuples Inserted: 0
   Conflicting Tuples: 1
   ->  Result  (cost=0.00..0.01 rows=1 width=36) (actual
time=0.002..0.002 rows=1 loops=1)
 Planning Time: 0.034 ms
 Execution Time: 0.065 ms
(10 rows)

So if we are able to efficienty execute query above, why we can not
write query:

INSERT INTO jsonb_schemas (schema) VALUES (obj_schema)
ON CONFLICT (schema) DO SELECT ID RETURNING id

--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company