Thread: [GENERAL] A possible use case for: "INSERT .. ON CONFLICT DO SELECT [FOR ..]"

Hi,

Since I have no subscription for pgsql-hackers, I will try to answer the question for comments raised in a discussion
onthe topic [1] in this way. 

[1]
https://www.postgresql.org/message-id/flat/CAL9smLAu8X7DLWdJ7NB0BtcN%3D_kCz33Fz6WYUBDRysWdG0yFig%40mail.gmail.com#CAL9smLAu8X7DLWdJ7NB0BtcN=_kCz33Fz6WYUBDRysWdG0yFig@mail.gmail.com


Perhaps I misunderstand the discussion but would "INSERT .. ON CONFLICT DO SELECT [FOR ..]" not provide a solution for
thefollowing use case? 


A table should contain a text label and an ID. The ID is to be used as a foreign key in other tables.
If a text label does not yet exist, create a new entry in the table and return the ID. If the label already exists, the
IDshould also be returned. 


create table upsert_with_serial_test(id serial, uniq_text text, primary key(id));
create unique index on upsert_with_serial_test (uniq_text);


insert into upsert_with_serial_test (uniq_text) values('t1') on conflict (uniq_text) do nothing returning *;
id | uniq_text
----+----------- 1 | t1
(1 row)


insert into upsert_with_serial_test (uniq_text) values('t1') on conflict (uniq_text) do nothing returning *;
id | uniq_text
----+-----------
(0 rows)

no insert > no id

--

insert into upsert_with_serial_test as t (uniq_text) values('t1') on conflict (uniq_text) do update set uniq_text =
t.uniq_textreturning *; 
id | uniq_text
----+----------- 1 | t1
(1 row)


insert into upsert_with_serial_test as t (uniq_text) values('t2') on conflict (uniq_text) do update set uniq_text =
t.uniq_textreturning *; 
id | uniq_text
----+----------- 4 | t2
(1 row)


That works. But it is a bit inconvenient to write the pseudo update clause.



Regards,
Marc-Olaf Jaschke





--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general