how to emulate "insert ignore" and "on duplicate key update" (sql merge) with postgresql?

Question

Some SQL servers have a feature where INSERT is skipped if it would violate a primary/unique key constraint. For instance, MySQL has INSERT IGNORE.

What's the best way to emulate INSERT IGNORE and ON DUPLICATE KEY UPDATE with PostgreSQL?

Answer 1

With PostgreSQL 9.5, this is now native functionality (like MySQL has had for several years):

INSERT ... ON CONFLICT DO NOTHING/UPDATE ("UPSERT")

9.5 brings support for "UPSERT" operations. INSERT is extended to accept an ON CONFLICT DO UPDATE/IGNORE clause. This clause specifies an alternative action to take in the event of a would-be duplicate violation.

...

Further example of new syntax:

INSERT INTO user_logins (username, logins)
VALUES ('Naomi',1),('James',1) 
ON CONFLICT (username)
DO UPDATE SET logins = user_logins.logins + EXCLUDED.logins;