MERGE syntax used to UPSERT or INSERT on duplicate UPDATE

Question

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?

score 14 · Accepted Answer · edited May 23 '17 at 12:29

14

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

edited May 23 '17 at 12:29

Community

1
1

answered Dec 30 '10 at 01:33

Michael Buen

38,643
9
94
118

what version of Postgres is this supported on? – Phill Pafford Jan 17 '11 at 19:36

score 3 · Answer 2 · answered Dec 29 '10 at 23:22

3

Until merge is supported the simplest way IMO is to just break it up into two queries:

BEGIN;
  INSERT INTO t (a,b,c) VALUES (1,2,3) WHERE id != 1;
  UPDATE t SET c=c+1 WHERE id = 1;
END;

where id would be changed to the appropriate condition.

answered Dec 29 '10 at 23:22

nate c

8,802
2
27
28

hmm I like this approach even though it is costing you two queries – Phill Pafford Dec 30 '10 at 19:29
hmm, not working in testing. gives me an error on the WHERE condition – Phill Pafford Jan 12 '11 at 19:15

score 0 · Answer 3 · answered Dec 29 '10 at 17:26

MERGE INTO table
    USING (VALUES (1, 2, 3)) AS newvalues (a, b, c)
    ON table.c = newvalues.c  -- or whatever the PK is
    WHEN MATCHED THEN UPDATE SET c = c + 1
    WHEN NOT MATCHED THEN INSERT (a, b, c)
                              VALUES (newvalues.a, newvalues.b, newvalues.c)

The key here is that instead of merging in another table you create a constant table source using the VALUES construct in the USING clause. The exact merging rules you can obviously tailor to taste.

See also http://petereisentraut.blogspot.com/2010/05/merge-syntax.html.

patch is 'waiting on author' - https://commitfest.postgresql.org/action/commitfest_view/inprogress — nate c, Dec 29 '10 at 23:10

DrColossos · Answer 4 · 2010-12-29T19:19:52.823

0

I think "MERGE" is not yet in Postgres but is suposed to be in 9.1.

I like to use RULEs instead

CREATE OR REPLACE RULE "insert_ignore"
AS ON INSERT TO "table" WHERE
  NEW.id = OLD.id --whatever your conditions are
DO INSTEAD NOTHING;

What you have linked to ("Insert, on duplicate update (postgresql)") is basically some pgsql that you feed the data. I think the RULE is more elegant since you don't need to call them explicitly and they work transparent in the background without the need to call a procedure within your actual INSERT.

edited Dec 29 '10 at 19:19

answered Dec 29 '10 at 19:12

DrColossos

12,656
3
46
67

So would this be a INSERT else IGNORE/SKIP? – Phill Pafford Dec 29 '10 at 19:49
It's an IGNORE/SKIP since you check for existence and if so, skip it. The same will work for UPDATE actions. Consider RULEs as "a kind of trigger" that are able to modify the query itself by rewriting it or (as in this case) ignoring parts (or all) of it. – DrColossos Dec 30 '10 at 07:27
1

I did this but the OLD.id gave an error, didn't like the value. Any thoughts? – Phill Pafford Dec 30 '10 at 19:30
This was actually just an example. You could write any query you want into the where. Look through the manual to see the examples. – DrColossos Dec 31 '10 at 09:46
1

@PhillPafford Apparently the metarow "OLD" doesn't exist on INSERT rules, but does for UPDATE rules. Still haven't figured it out yet though... – Trey Stout Oct 11 '11 at 01:45

MERGE syntax used to UPSERT or INSERT on duplicate UPDATE

4 Answers4

Linked