MERGE syntax used to UPSERT or INSERT on duplicate UPDATE

postgresql upsert sql
Phill Pafford picture Phill Pafford · Dec 29, 2010 · Viewed 20.6k times · Source

So I'm coming from MySQL where I could do INSERT on DUPLICATE UPDATE:

INSERT INTO table (a,b,c) 
VALUES (1,2,3)
ON DUPLICATE KEY UPDATE c=c+1;

But now I'm using PostgreSQL and there are efforts to add the UPSERT functionality, looks like MERGE might work for what I would like but wanted to see if this is the most optimal syntax. Example Syntax 1, I've also seen this but don't understand how to implement. I haven't tried this yet because I thought MERGE was used for merging data from table1 to Table2 or would something like this work?

MERGE
INTO    table
USING   table
ON      c = 1
WHEN MATCHED THEN
UPDATE
SET     c=c+1
WHEN NOT MATCHED THEN
INSERT  (a,b,c)
VALUES  (1,2,3)

Any other suggestions?

Answer

Michael Buen picture Michael Buen · Dec 30, 2010

Until MERGE is available, use this robust approach: Insert, on duplicate update in PostgreSQL?

Related questions

Insert, on duplicate update in PostgreSQL?

Several months ago I learned from an answer on Stack Overflow how to perform multiple updates at once in MySQL using the following syntax: INSERT INTO table (id, field, field2) VALUES (1, A, X), (2, B, Y), (3, C, Z) ON DUPLICATE KEY …

Read More
How do I UPDATE a row in a table or INSERT it if it doesn't exist?

I have the following table of counters: CREATE TABLE cache ( key text PRIMARY KEY, generation int ); I would like to increment one of the counters, or set it to zero if the corresponding row doesn't exist yet. Is there a …

Read More
How to use RETURNING with ON CONFLICT in PostgreSQL?

I have the following UPSERT in PostgreSQL 9.5: INSERT INTO chats ("user", "contact", "name") VALUES ($1, $2, $3), ($2, $1, NULL) ON CONFLICT("user", "contact") DO NOTHING RETURNING id; If there are no conflicts it returns something like this: ---------- | id | ---------- 1 | 50 | ---------- 2 | 51 | ---------- But if …

Read More