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.knizhnik@postgrespro.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