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 on the topic [1] in this way.

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

Perhaps I misunderstand the discussion but would "INSERT .. ON CONFLICT DO SELECT [FOR ..]" not provide a solution for the following 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 ID should 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_text returning *;

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_text returning *;

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