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