Postgresql，如果存在具有某个唯一值的行，则更新，否则插入
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11135501/
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, update if row with some unique value exists, else insert
提问by Roman Lebedev
I have a URLs table. They contain
我有一个 URL 表。他们包含
(id int primary key, url character varying unique, content character varying, last analyzed date).
（id int 主键，url 字符不同，内容不同，最后分析日期）。
I want to create trigger or something(rule may be), so each time i make insert from my java program, it updates some single row if row with such URL exists. Else it should perform an Insert.
我想创建触发器或其他东西（规则可能是），所以每次我从我的 Java 程序插入时，如果存在具有此类 URL 的行，它会更新一些单行。否则它应该执行插入。
Please, can you provide a complete code in Postgresql. Thanks.
请问，你能在Postgresql中提供完整的代码吗？谢谢。
回答by Dave Halter
This has been asked many times. A possible solution can be found here: https://stackoverflow.com/a/6527838/552671
这已经被问过很多次了。可以在此处找到可能的解决方案：https: //stackoverflow.com/a/6527838/552671
This solution requires both an UPDATEand INSERT.
此解决方案需要UPDATE和INSERT。
UPDATE table SET field='C', field2='Z' WHERE id=3;
INSERT INTO table (id, field, field2)
SELECT 3, 'C', 'Z'
WHERE NOT EXISTS (SELECT 1 FROM table WHERE id=3);
With Postgres 9.1 it is possible to do it with one query: https://stackoverflow.com/a/1109198/2873507
使用 Postgres 9.1，可以通过一个查询来完成：https: //stackoverflow.com/a/1109198/2873507
回答by chribsen
If INSERTSare rare, I would avoid doing a NOT EXISTS (...)since it emits a SELECTon all updates. Instead, take a look at wildpeaks answer: https://dba.stackexchange.com/questions/5815/how-can-i-insert-if-key-not-exist-with-postgresql
如果INSERTS很少见，我会避免执行 a，NOT EXISTS (...)因为它会SELECT在所有更新上发出 a 。相反，看看wildpeaks答案：https://dba.stackexchange.com/questions/5815/how-can-i-insert-if-key-not-exist-with-postgresql
CREATE OR REPLACE FUNCTION upsert_tableName(arg1 type, arg2 type) RETURNS VOID AS $$
DECLARE
BEGIN
UPDATE tableName SET col1 = value WHERE colX = arg1 and colY = arg2;
IF NOT FOUND THEN
INSERT INTO tableName values (value, arg1, arg2);
END IF;
END;
$$ LANGUAGE 'plpgsql';
This way Postgres will initially try to do a UPDATE. If no rows was affected, it will fall back to emitting an INSERT.
这样 Postgres 最初会尝试做一个UPDATE. 如果没有行受到影响，它将回退到发出INSERT.
回答by dhruvpatel
回答by Tahtakafa
Firstly It tries insert. If there is a conflict on urlcolumn then it updates content and last_analyzed fields. If updates are rare this might be better option.
首先它尝试插入。如果url列上存在冲突，则它会更新内容和 last_analyzed 字段。如果更新很少，这可能是更好的选择。
INSERT INTO URLs (url, content, last_analyzed)
VALUES
(
%(url)s,
%(content)s,
NOW()
)
ON CONFLICT (url)
DO
UPDATE
SET content=%(content)s, last_analyzed = NOW();
