0

I've expected that the SQL-statement INSERT ... RETURNING * would always return something but it seems to work only after the first insertion. The following insertions don't fail but also don't return anything. Is it the expected behavior of the statement?

I have a table identities that has a unique constraint on the columns mrn and name. I want to insert a record:

INSERT INTO identities (mrn, name) VALUES('x', 'y') on conflict do nothing returning id;

I get the id as expected 1b3e43cc-722a-4703-91d3-6839287b6eff

But the second insert doesn't return anything.

ka3ak
  • 2,435
  • 2
  • 30
  • 57
  • 1
    Yes, it's expected. `returning id` only returns a row if something go inserted. See e.g. [here](https://stackoverflow.com/questions/40323799/return-rows-from-insert-with-on-conflict-without-needing-to-update) –  Aug 31 '21 at 08:57
  • @a_horse_with_no_name Strange. It would be much better (and logical) if the statement helped me to identify the existing record... – ka3ak Aug 31 '21 at 08:59
  • 1
    Does this answer your question? [SELECT or INSERT a row in one command](https://stackoverflow.com/questions/6722344/select-or-insert-a-row-in-one-command) also [this answer](https://stackoverflow.com/a/42217872/1048572) – Bergi Aug 31 '21 at 09:03
  • @Bergi Yes. But I've created a slightly different query based on the answer there. I `do update` on a column just to force `returning` to return the row. Thanks! – ka3ak Sep 01 '21 at 11:05

0 Answers0