3

I have the following query right now which returns the id after a successful insert

INSERT INTO match (match_id) values(?) ON CONFLICT DO NOTHING RETURNING id

How can I make this return the id of the row which already exists on conflict?

Arya
  • 8,473
  • 27
  • 105
  • 175
  • Check [this answer](https://stackoverflow.com/a/42217872/2996101), which states "do not update identical rows without need". – raratiru Apr 15 '18 at 16:57

1 Answers1

6

Use ON CONFLICT DO UPDATE instead to ensure that the query operates on a row and so can return something:

CREATE TABLE match(id serial PRIMARY KEY);

INSERT INTO match (id) VALUES (1)
ON CONFLICT (id) DO UPDATE
  SET id = excluded.id
RETURNING id;
dmfay
  • 2,417
  • 1
  • 11
  • 22
  • I get the following error "org.postgresql.util.PSQLException: ERROR: ON CONFLICT DO UPDATE requires inference specification or constraint name Hint: For example, ON CONFLICT (column_name). Position: 41" – Arya Oct 15 '17 at 04:12
  • Also I wanted to mention, it's `match_id` that I'm setting, and I want `id` returned – Arya Oct 15 '17 at 04:15
  • My bad, you have to specify what's conflicting -- I've edited. You can specify what you're updating and returning however you like, although unless `id` is based on `match_id` I'm not sure how you're going to trigger the conflict without explicitly specifying it in your insert. – dmfay Oct 15 '17 at 04:18
  • 1
    The following works `INSERT INTO match (match_id) VALUES (33333) ON CONFLICT (match_id) DO UPDATE SET match_id = excluded.match_id RETURNING id;` however on every conflict my sequence increases. Is that supposed to happen? no way around it? – Arya Oct 15 '17 at 04:24
  • You could use a non-serial key like a UUID. Otherwise, serial ids are guaranteed to be unique, not to be contiguous. Gaps happen. – dmfay Oct 15 '17 at 04:30
  • @arya, if you replace "excluded" with "match", it should return the existing id instead of incrementing it. This answer should be modified. – Puma Nov 22 '20 at 06:38
  • It might not be clear, but `excluded` is a "special" table that you only can access in `SET` and `WHERE` clauses of `ON CONFLICT DO UPDATE` statements.... Cool! https://www.postgresql.org/docs/current/sql-insert.html#SQL-ON-CONFLICT. – t-mart Apr 08 '23 at 07:48