Back

Explore Courses Blog Tutorials Interview Questions
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!

28.4k questions

29.7k answers

500 comments

94.2k users

Browse Categories

...