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

by

A very frequently asked question here is how to do an upsert, which is what MySQL calls INSERT ... ON DUPLICATE UPDATE and the standard supports as part of the MERGE operation.

Given that PostgreSQL doesn’t support it directly (before pg 9.5), how do you do this? Consider the following:

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

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

Now imagine that you want to “upsert” the tuples (2, 'Joe'), (3, 'Alan'), so the new table contents would be:

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

That’s what people are talking about when discussing an upsert. Crucially, any approach must be safe in the presence of multiple transactions working on the same table – either by using explicit locking, or otherwise defending against the resulting race conditions.

This topic is discussed extensively at Insert, on duplicate update in PostgreSQL?, but that’s about alternatives to the MySQL syntax, and it’s grown a fair bit of unrelated detail over time. I’m working on definitive answers.

These techniques are also useful for “insert if not exists, otherwise do nothing”, i.e. “insert … on duplicate key ignore”.

6 Answers
6

Leave a Comment