From: | Konstantin Knizhnik <k(dot)knizhnik(at)postgrespro(dot)ru> |
---|---|
To: | PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: INSERT ON CONFLICT and RETURNING |
Date: | 2020-09-03 16:16:14 |
Message-ID: | 79d47870-4280-c6fd-d498-e99bae03c706@postgrespro.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On 22.08.2020 10:16, Konstantin Knizhnik wrote:
> 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
I'm sorry for been intrusive.
But can somebody familiar with Postgres upsert mechanism explain me why
current implementation doesn't support very popular use case:
locate record by some unique key and and return its primary
(autogenerated) key if found otherwise insert new tuple.
I have explained the possible workarounds of the problem above.
But all of them looks awful or inefficient.
What I am suggesting is just add ON CONFLICT DO SELECT clause:
insert into jsonb_schemas (schema) values ('one') on conflict(schema) do
select returning id;
I attached small patch with prototype implementation of this construction.
It seems to be very trivial. What's wring with it?
Are there some fundamental problems which I do not understand?
Below is small illustration of how this patch is working:
postgres=# create table jsonb_schemas(id serial, schema bytea primary key);
CREATE TABLE
postgres=# create index on jsonb_schemas(id);
CREATE INDEX
postgres=# insert into jsonb_schemas (schema) values ('some') on
conflict(schema) do nothing returning id;
id
----
1
(1 row)
INSERT 0 1
postgres=# insert into jsonb_schemas (schema) values ('some') on
conflict(schema) do nothing returning id;
id
----
(0 rows)
INSERT 0 0
postgres=# insert into jsonb_schemas (schema) values ('some') on
conflict(schema) do select returning id;
id
----
1
(1 row)
INSERT 0 1
Thanks in advance,
Konstantin
Attachment | Content-Type | Size |
---|---|---|
on_conflict_do_select.patch | text/x-patch | 6.9 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2020-09-03 16:17:51 | Re: Allow continuations in "pg_hba.conf" files |
Previous Message | Dave Page | 2020-09-03 15:54:37 | Re: Kerberos support broken on MSVC builds for Windows x64? |