Re: INSERT ON CONFLICT and RETURNING

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