On 24.08.2020 13:37, Geoff Winkless wrote:
On Sat, 22 Aug 2020 at 08:16, Konstantin Knizhnik
<k.knizh...@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
