0 votes
1 view
in SQL by (22.4k 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.3k 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

Welcome to Intellipaat Community. Get your technical queries answered by top developers !


Categories

...