0 votes
1 view
in SQL by (20.3k points)

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?

1 Answer

0 votes
by (40.4k points)

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.

Related questions

0 votes
1 answer
0 votes
1 answer
0 votes
1 answer
asked Dec 13, 2020 in SQL by Appu (6.1k points)
Welcome to Intellipaat Community. Get your technical queries answered by top developers !


Categories

...