Hi hackers,
I am sorry for the question which may be already discussed multiple times.
But I have not found answer for it neither in internet neither in
pgsql-hackers archieve.
UPSERT (INSERT ... IN CONFLICT...) clause was added to the Postgres a
long time ago.
As far as I remember there was long discussions about its syntax and
functionality.
But today I found that there is still no way to perform one of the most
frequently needed operation:
locate record by key and return its autogenerated ID or insert new
record if key is absent.
Something like this:
create table jsonb_schemas(id serial, schema bytea primary key);
create index on jsonb_schemas(id);
insert into jsonb_schemas (schema) values (?) on conflict(schema) do
nothing returning id;
But it doesn't work because in case of conflict no value is returned.
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.
Or perform update:
insert into jsonb_schemas (schema) values (?) on conflict(schema) do
update set schema=excluded.schema returning id;
But it is even worse because we have to perform useless update and
produce new version.
May be I missing something, but according to stackoverflow:
https://stackoverflow.com/questions/34708509/how-to-use-returning-with-on-conflict-in-postgresql
there is no better solution.
I wonder how it can happen that such popular use case ia not covered by
Postgresql UPSERT?
Are there some principle problems with it?
Why it is not possible to add one more on-conflict action: SELECT,
making it possible to return data when key is found?
Thanks in advance,
Konstantin