On Mon, 31 Aug 2020 at 14:53, Konstantin Knizhnik <k.knizh...@postgrespro.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
