zola zola - 1 month ago 9
SQL Question

How to use RETURNING with ON CONFLICT in PostgreSQL?

I am using PostgreSQL 9.5 and i have the following:

INSERT INTO chats ("user", "contact", "name")
VALUES ($1, $2, $3),
($2, $1, NULL)
ON CONFLICT("user", "contact") DO NOTHING RETURNING id;


if there are no conflicts it returns something like this

----------
| id |
----------
1 | 50 |
----------
2 | 51 |
----------


but if there are conflicts it doesn't return any rows

----------
| id |
----------


but what i want to do is return the new
id
columns if there are no conflicts or return the existing
id
columns of the conflicting columns. Can this be done? If so, how?

Answer

I had exactly the same problem, and I solved it using 'do update' instead of 'do nothing', even though I had nothing to update. In your case it would be something like this:

INSERT INTO chats ("user", "contact", "name") 
       VALUES ($1, $2, $3), 
              ($2, $1, NULL) 
ON CONFLICT("user", "contact") DO UPDATE SET name=EXCLUDED.name RETURNING id;

This query will return all the rows, regardless they have just been inserted or they existed before.

Comments