PostgreSQL INSERT ON CONFLICT UPDATE (upsert) 使用所有排除的值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/36359440/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL INSERT ON CONFLICT UPDATE (upsert) use all excluded values
提问by Sebastian
When you are upserting a row (PostgreSQL >= 9.5), and you want the possible INSERT to be exactly the same as the possible UPDATE, you can write it like this:
当您更新一行（PostgreSQL >= 9.5），并且您希望可能的 INSERT 与可能的 UPDATE 完全相同时，您可以这样写：
INSERT INTO tablename (id, username, password, level, email)
VALUES (1, 'John', 'qwerty', 5, '[email protected]')
ON CONFLICT (id) DO UPDATE SET
id=EXCLUDED.id, username=EXCLUDED.username,
password=EXCLUDED.password, level=EXCLUDED.level,email=EXCLUDED.email
Is there a shorter way? To just say: use all the EXCLUDE values.
有没有更短的方法？只是说：使用所有 EXCLUDE 值。
In SQLite I used to do :
在 SQLite 中，我曾经这样做：
INSERT OR REPLACE INTO tablename (id, user, password, level, email)
VALUES (1, 'John', 'qwerty', 5, '[email protected]')
回答by Kristján
Postgres hasn't implemented an equivalent to INSERT OR REPLACE. From the ON CONFLICTdocs(emphasis mine):
Postgres 尚未实现与INSERT OR REPLACE. 从ON CONFLICT文档（强调我的）：
It can be either DO NOTHING, or a DO UPDATE clause specifying the exact detailsof the UPDATE action to be performed in case of a conflict.
它可以是 DO NOTHING 或 DO UPDATE 子句，指定在发生冲突时要执行的 UPDATE 操作的确切细节。
Though it doesn't give you shorthand for replacement, ON CONFLICT DO UPDATEapplies more generally, since it lets you set new values based on preexisting data. For example:
虽然它没有为您提供替换的简写，但ON CONFLICT DO UPDATE更普遍地适用，因为它允许您根据预先存在的数据设置新值。例如：
INSERT INTO users (id, level)
VALUES (1, 0)
ON CONFLICT (id) DO UPDATE
SET level = users.level + 1;
