PostgreSQL INSERT ON CONFLICT UPDATE (upsert) use all excluded values

PostgreSQL INSERT ON CONFLICT UPDATE (upsert) - Use All EXCLUDED Values! 🚀

Have you ever wondered if there's a shorter and simpler way to perform an UPSERT operation in PostgreSQL? You know, to effortlessly handle conflict errors when inserting a row and updating it if it already exists? Well, good news! 🎉 We have a neat solution for you!

Understanding the Problem 😕

Let's say you have a table called tablename with columns like id, username, password, level, and email. You want to upsert a row with specific values, but you also want the possible INSERT to be exactly the same as the possible UPDATE.

In PostgreSQL versions 9.5 and above, you can accomplish this by using the ON CONFLICT clause along with the DO UPDATE statement and explicitly setting each column's value using the EXCLUDED keyword. Here's an example:

INSERT INTO tablename (id, username, password, level, email) 
VALUES (1, 'John', 'qwerty', 5, 'john@mail.com') 
ON CONFLICT (id) DO UPDATE SET 
  id=EXCLUDED.id, username=EXCLUDED.username,
  password=EXCLUDED.password, level=EXCLUDED.level, email=EXCLUDED.email;

Easier and Simpler Solution: 🤩

But wait, is there a shorter way to achieve the same result? Fortunately, there is! 🙌 In PostgreSQL, you can leverage the power of the INSERT INTO ... VALUES ... ON CONFLICT DO NOTHING statement to automatically use all the excluded values without explicitly mentioning each column. Here's how it looks:

INSERT INTO tablename (id, username, password, level, email) 
VALUES (1, 'John', 'qwerty', 5, 'john@mail.com') 
ON CONFLICT (id) DO NOTHING;

Wow, that's much more concise and expressive, right? By using DO NOTHING, PostgreSQL will effectively perform an INSERT if there's no conflict or gracefully skip the row if a conflict occurs. This saves you from the hassle of explicitly specifying all the column values in the UPDATE clause.

Comparing with SQLite: 🔄

If you're familiar with SQLite, you might recall using the INSERT OR REPLACE INTO ... syntax for similar upsert operations. However, PostgreSQL follows a slightly different approach. Though the syntax differs, the functionality is essentially the same.

For reference, here's how you would achieve the upsert operation in SQLite:

INSERT OR REPLACE INTO tablename (id, username, password, level, email) 
VALUES (1, 'John', 'qwerty', 5, 'john@mail.com');

Conclusion and Call-to-Action 📝

You've learned a powerful technique to simplify your UPSERT operations in PostgreSQL using the ON CONFLICT DO NOTHING statement. By using this concise approach, you can effortlessly handle conflicts and take advantage of the excluded values without explicitly mentioning each one. 🚀

Now it's your turn to try it out in your own projects and see the magic unfold! Let us know in the comments how this technique has helped you and if you have any other cool tips to share. Happy coding! 💻💡

Check out our blog for more useful PostgreSQL tips and tricks. Don't forget to subscribe to stay updated with the latest articles and join our community of developers! Let's level up our PostgreSQL game together! 🙌