Re: INSERT ON CONFLICT and RETURNING

On Mon, 31 Aug 2020 at 14:53, Konstantin Knizhnik
<k(dot)knizhnik(at)postgrespro(dot)ru> wrote:
> 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:

The conflict resolution checks the unique index on (schema) and
decides whether or not a conflict will exist. For DO NOTHING it
doesn't have to get the actual row from the table; however in order
for it to return the ID it would have to go and get the existing row
from the table. That's the "extra lookup", as you term it. The only
difference from doing it with RETURNING id versus WITH... COALESCE()
as you described is the simpler syntax.

I'm not saying the simpler syntax isn't nice, mind you. I was just
pointing out that it's not inherently any less efficient.

Geoff