1

I want to insert rows into a table and return the identifier of the inserted row. Whenever a unique contraint is conflicted, I still want to return the identifier.

I ran the following query:

insert into operations.my_table (domain, email) 
VALUES ('my_domain', 'email@my_domain') 
ON CONFLICT do nothing 
returning my_table_id

However, it does not return anything on conflict.

Jeroen Vermunt
  • 672
  • 1
  • 6
  • 19
  • 3
    Since it does nothing, there's nothing to return. A dirty approach would be to do a neutral update `on conflict do update set (domain,email)=(domain,email)`. You could also enclose your values this in a `with` CTE, run your insert in another one, then run a `select my_table_id from operations.my_table where (comain,email) in (select domain,email from valuelist_cte) union select my_table_id from insert_returning_cte`. EDIT: turns out that was pretty much the bottom line [here](https://stackoverflow.com/q/34708509/5298879). – Zegarek Dec 31 '22 at 15:31
  • @Bergi this indeed answers my question – Jeroen Vermunt Dec 31 '22 at 15:52

0 Answers0