How to use RETURNING with ON CONFLICT in PostgreSQL?

Question

I have the following UPSERT in PostgreSQL 9.5:

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 |

----------

I want to 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 1

You can solve it using 'do update' instead of 'do nothing', even if there is nothing to update. 

Use the below code:

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;

Note: The above query will return all the rows, even though they have just been inserted or they existed before.