postgresql - 如何用postgresql模拟 "insert ignore"和 "on duplicate key update"(sql合并)？

Question

某些 SQL 服务器具有一项功能，如果 INSERT 违反主键/唯一键约束，则跳过该功能。例如，MySQL 有 INSERT IGNORE。

用 PostgreSQL 模拟 INSERT IGNORE 和 ON DUPLICATE KEY UPDATE 的最佳方法是什么？

Answer 1

对于 PostgreSQL 9.5，现在是 native functionality (像 MySQL has had 好几年了):

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;