MERGE syntax used to UPSERT or INSERT on duplicate UPDATE

8
votes

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?

4
postgresqlupsertsql

4 Answers

14
votes

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

3
votes

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.

0
votes
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.

0
votes

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.