How to UPSERT (MERGE, INSERT ... ON DUPLICATE UPDATE) in PostgreSQL?

How to UPSERT (MERGE, INSERT ... ON DUPLICATE UPDATE) in PostgreSQL?

📝 Hey there tech enthusiasts! Today we're diving into the thrilling world of PostgreSQL and tackling a frequently asked question: How do you perform an UPSERT operation in PostgreSQL? 🚀

🔎 UPSERT, also known as MERGE or INSERT ... ON DUPLICATE UPDATE, is a handy operation that allows you to either insert a new row into a table or update an existing row if it already exists. Unfortunately, PostgreSQL didn't support this operation directly until version 9.5. But fret not, we've got you covered with some cool workarounds! 😎

💡 Let's start by setting the stage. Imagine we have a table called testtable with the following structure:

CREATE TABLE testtable (
    id integer PRIMARY KEY,
    somedata text NOT NULL
);

INSERT INTO testtable (id, somedata) VALUES
(1, 'fred'),
(2, 'bob');

🔧 Now, let's say we want to UPSERT the tuples (2, 'Joe') and (3, 'Alan') into our testtable:

(1, 'fred'),
(2, 'Joe'),    -- Changed value of existing tuple
(3, 'Alan')    -- Added new tuple

✅ To accomplish this, there are a few techniques you can use. The crucial thing is to ensure that your approach is safe in the presence of multiple transactions working on the same table to avoid any race conditions. Here are a couple of options:

1. Using a CTE (Common Table Expression)

One way to achieve an UPSERT in PostgreSQL is by leveraging a CTE. Here's how you can do it:

WITH upsert AS (
    UPDATE testtable
    SET somedata = 'Joe'
    WHERE id = 2
    RETURNING *
)
INSERT INTO testtable (id, somedata)
SELECT 2, 'Joe'
WHERE NOT EXISTS (SELECT * FROM upsert);

In this approach, we first try to update the existing row with the new values using an UPDATE statement. If the row doesn't exist, the UPDATE statement won't affect any rows and will return an empty result set. Then, we use the INSERT INTO statement to insert a new row only if the UPDATE didn't modify any rows. Neat, right?

2. Using a combination of INSERT and ON CONFLICT

Another way to achieve the UPSERT operation in PostgreSQL is by utilizing the INSERT statement with the ON CONFLICT DO UPDATE clause. Here's an example:

INSERT INTO testtable (id, somedata)
VALUES (2, 'Joe')
ON CONFLICT (id) DO
    UPDATE SET somedata = excluded.somedata;

In this approach, the INSERT statement tries to insert a new row, and if a conflict occurs (i.e., the primary key already exists), the DO UPDATE clause kicks in and updates the existing row with the new values.

💡 These techniques are not only useful for UPSERTs but also for performing "insert if not exists, otherwise do nothing" operations.

📚 If you want to delve deeper into this topic, head over to Stack Overflow. Just note that it might have some unrelated details, as it's grown over time. But hey, definitive answers are always a work in progress! 😉

🌟 Now that you have two powerful techniques in your arsenal, go ahead and UPSERT with confidence in PostgreSQL! If you have any more questions or need further clarification, drop a comment below. Let's keep the tech conversation going! 👇