Tag: upsert

Insert or Update? Upserts in Three Popular Databases

When working with databases, a very common task is this: you want to store a row, but you don’t know if it already exists. If it doesn’t exist, you insert it. If it does exist, you update it. That is what is called an upsert.

Without upsert support, you would first run a SELECT, then decide whether to INSERT or UPDATE. That works, but it has two problems. First, it’s slower because you need multiple queries. Second, it can break under concurrency: two processes might try to insert the same row at the same time. Upsert solves this by doing everything in one step inside the database.

PostgreSQL

PostgreSQL has a very direct way to express this. You try to insert a row, and if there is a conflict, you say what should happen instead:

INSERT INTO users (id, name, email)
VALUES (1, 'Alice', 'alice@example.com')
ON CONFLICT (id)
DO UPDATE SET
    name = EXCLUDED.name,
    email = EXCLUDED.email;

The important part is ON CONFLICT (id). It means: if the id already exists, don’t fail. Just update the row. EXCLUDED is just the new data you tried to insert.

This is easy to read and gives you good control, because you can clearly say which column causes the conflict.

MySQL

MySQL does something very similar, but a bit simpler and less explicit:

INSERT INTO users (id, name, email)
VALUES (1, 'Alice', 'alice@example.com')
ON DUPLICATE KEY UPDATE
    name = VALUES(name),
    email = VALUES(email);

Here, MySQL automatically reacts when a primary key or unique key is violated. You don’t explicitly name the column. It just uses whatever key causes the problem. This makes the syntax shorter, but also a bit less clear if your table has multiple unique constraints.

Oracle

Oracle uses a different concept called MERGE. It looks more complicated, but it is also more flexible:

MERGE INTO users u
USING (SELECT 1 AS id, 'Alice' AS name, 'alice@example.com' AS email FROM dual) src
ON (u.id = src.id)
WHEN MATCHED THEN
    UPDATE SET
        u.name = src.name,
        u.email = src.email
WHEN NOT MATCHED THEN
    INSERT (id, name, email)
    VALUES (src.id, src.name, src.email);

Instead of saying “insert and maybe update”, you describe how two datasets relate to each other. If a row matches, you update it. If not, you insert it. For simple cases, this feels a bit heavy. But if you need more complex logic, MERGE becomes very useful.

Can you write one query that works everywhere?

Unfortunately, the answer is: Not really. There is no single SQL statement that works the same way in Oracle, PostgreSQL, and MySQL. The syntax is just too different. You can write something that works everywhere by doing it in two steps, like this:

UPDATE users
SET name = 'Alice', email = 'alice@example.com'
WHERE id = 1;
INSERT INTO users (id, name, email)
SELECT 1, 'Alice', 'alice@example.com'
WHERE NOT EXISTS (
    SELECT 1 FROM users WHERE id = 1
);

This works in all three databases, but it’s not perfect. It’s not truly atomic unless you handle transactions very carefully. Because of that, many people don’t try to force a single SQL solution. Instead, they use different queries per database or let a framework handle it.