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
