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.gmail.com#CAL9smLAu8X7DLWdJ7NB0BtcN=_kcz33fz6wyubdryswdg0y...@mail.gmail.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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
