How to use RETURNING with ON CONFLICT in PostgreSQL?

How to use RETURNING with ON CONFLICT in PostgreSQL?

So you're trying to perform an UPSERT operation in PostgreSQL and you want to retrieve the new id values when there are no conflicts, or the existing id values when there are conflicts. Sounds like a tough nut to crack, right? But fear not! I've got you covered. 😎

Let's dive into the details and see how you can achieve this using the RETURNING clause in conjunction with the ON CONFLICT clause.

Understanding the UPSERT operation

Before we get into the technical nitty-gritty, let's quickly recap what an UPSERT operation actually does. UPSERT is a combination of INSERT and UPDATE operations. It allows you to insert a new row into a table, or update the existing row if a conflict occurs. In your case, you only want to perform the INSERT if there are no conflicts (i.e., the combination of "user" and "contact" doesn't already exist), and you want to retrieve the id values in both cases.

The syntax

The UPSERT statement you provided looks almost good to go. Just a slight modification is needed to achieve the desired result. Here's the modified version for your reference:

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;

Let's break down what's happening here:

1. We specify the target table name, followed by the column names in the INSERT clause.

2. We use the VALUES keyword to provide the values for the columns, using parameter placeholders ($1, $2, $3 in this case).

3. The ON CONFLICT clause specifies the columns to check for conflicts. In your case, it's "user" and "contact".

4. The DO UPDATE clause is used to perform the update action when a conflict occurs. Here, we're simply setting the "name" column to the excluded value (i.e., the value we tried to insert).

5. Lastly, the RETURNING keyword allows us to retrieve the id values of the affected rows, whether it's an insertion or an update.

Expected output

When you execute the modified UPSERT statement, you'll get the following output:

If there are no conflicts:

----------
| id |
----------
| 50 |
----------
| 51 |
----------

If there are conflicts (and the conflicting rows are successfully updated):

----------
| id |
----------
| 50 |
----------

🚀 Your turn to try it out!

Now that you're armed with the knowledge of how to use RETURNING with ON CONFLICT, it's time to put it into action. Give it a go and experiment with your own data. If you encounter any issues or have any questions, feel free to leave a comment below. I'll be more than happy to assist you! Let's rock this UPSERT operation! 💪

Your comments matter!

If this blog post was helpful to you, or if you'd like to share your experience with UPSERT in PostgreSQL, drop a comment below! I'd love to hear from you and learn about your unique use cases.

Don't forget to share this guide with your friends and colleagues who might find it useful. That way, we can spread the knowledge and make the tech world a better place. Happy coding! 😄✌️