插入，在PostgreSQL中重复更新吗？

本文翻译自：Insert, on duplicate update in PostgreSQL?

Several months ago I learned from an answer on Stack Overflow how to perform multiple updates at once in MySQL using the following syntax: 几个月前，我从关于堆栈溢出的答案中学到了如何使用以下语法在MySQL中一次执行多个更新：

INSERT INTO table (id, field, field2) VALUES (1, A, X), (2, B, Y), (3, C, Z)
ON DUPLICATE KEY UPDATE field=VALUES(Col1), field2=VALUES(Col2);

I've now switched over to PostgreSQL and apparently this is not correct. 我现在已经切换到PostgreSQL，显然这是不正确的。 It's referring to all the correct tables so I assume it's a matter of different keywords being used but I'm not sure where in the PostgreSQL documentation this is covered. 它指的是所有正确的表，因此我假设使用的是不同的关键字，但是我不确定在PostgreSQL文档的哪个地方覆盖。

To clarify, I want to insert several things and if they already exist to update them. 为了澄清，我想插入几件事，如果它们已经存在，请对其进行更新。

---

#1楼

参考：https://stackoom.com/question/4eW5/插入-在PostgreSQL中重复更新吗

---

#2楼

Personally, I've set up a "rule" attached to the insert statement. 就个人而言，我已经在插入语句上设置了一个“规则”。 Say you had a "dns" table that recorded dns hits per customer on a per-time basis: 假设您有一个“ dns”表，该表按时间记录了每个客户的dns命中：

CREATE TABLE dns (
    "time" timestamp without time zone NOT NULL,
    customer_id integer NOT NULL,
    hits integer
);

You wanted to be able to re-insert rows with updated values, or create them if they didn't exist already. 您希望能够重新插入具有更新值的行，或者如果还不存在则创建它们。 Keyed on the customer_id and the time. 键入customer_id和时间。 Something like this: 像这样：

CREATE RULE replace_dns AS 
    ON INSERT TO dns 
    WHERE (EXISTS (SELECT 1 FROM dns WHERE ((dns."time" = new."time") 
            AND (dns.customer_id = new.customer_id)))) 
    DO INSTEAD UPDATE dns 
        SET hits = new.hits 
        WHERE ((dns."time" = new."time") AND (dns.customer_id = new.customer_id));

Update: This has the potential to fail if simultaneous inserts are happening, as it will generate unique_violation exceptions. 更新：如果同时进行插入，则有可能失败，因为它将生成unique_violation异常。 However, the non-terminated transaction will continue and succeed, and you just need to repeat the terminated transaction. 但是，未终止的事务将继续并成功，您只需要重复终止的事务即可。

However, if there are tons of inserts happening all the time, you will want to put a table lock around the insert statements: SHARE ROW EXCLUSIVE locking will prevent any operations that could insert, delete or update rows in your target table. 但是，如果一直有大量的插入操作，您将需要在insert语句周围放置一个表锁定：SHARE ROW EXCLUSIVE锁定将阻止任何可能在目标表中插入，删除或更新行的操作。 However, updates that do not update the unique key are safe, so if you no operation will do this, use advisory locks instead. 但是，不更新唯一密钥的更新是安全的，因此，如果您没有任何操作将执行此操作，请改用咨询锁。

Also, the COPY command does not use RULES, so if you're inserting with COPY, you'll need to use triggers instead. 另外，COPY命令不使用RULES，因此，如果要使用COPY插入，则需要使用触发器。

---

#3楼

According the PostgreSQL documentation of the INSERT statement , handling the ON DUPLICATE KEY case is not supported. 根据INSERT语句的PostgreSQL文档，不支持处理ON DUPLICATE KEY大小写。 That part of the syntax is a proprietary MySQL extension. 语法的该​​部分是专有的MySQL扩展。

---

#4楼

There is no simple command to do it. 没有简单的命令可以执行此操作。

The most correct approach is to use function, like the one from docs . 最正确的方法是使用功能，例如docs中的功能 。

Another solution (although not that safe) is to do update with returning, check which rows were updates, and insert the rest of them 另一个解决方案（尽管不是很安全）是通过返回进行更新，检查哪些行已更新，然后插入其余行

Something along the lines of: 类似于以下内容：

update table
set column = x.column
from (values (1,'aa'),(2,'bb'),(3,'cc')) as x (id, column)
where table.id = x.id
returning id;

assuming id:2 was returned: 假设返回id：2：

insert into table (id, column) values (1, 'aa'), (3, 'cc');

Of course it will bail out sooner or later (in concurrent environment), as there is clear race condition in here, but usually it will work. 当然，它会早晚（在并发环境中）纾困，因为这里有明确的竞争条件，但通常它会起作用。

Here's a longer and more comprehensive article on the topic . 这是有关该主题的更长，更全面的文章 。

---

#5楼

PostgreSQL since version 9.5 has UPSERT syntax, with ON CONFLICT clause. 自9.5版起的PostgreSQL具有UPSERT语法，带有ON CONFLICT子句。 with the following syntax (similar to MySQL) 使用以下语法（类似于MySQL）

INSERT INTO the_table (id, column_1, column_2) 
VALUES (1, 'A', 'X'), (2, 'B', 'Y'), (3, 'C', 'Z')
ON CONFLICT (id) DO UPDATE 
  SET column_1 = excluded.column_1, 
      column_2 = excluded.column_2;

---

Searching postgresql's email group archives for "upsert" leads to finding an example of doing what you possibly want to do, in the manual : 在postgresql的电子邮件组档案中搜索“ upsert”，会在手册中找到一个示例，说明您可能想要做的事情 ：

Example 38-2. 示例38-2 Exceptions with UPDATE/INSERT UPDATE / INSERT的例外

This example uses exception handling to perform either UPDATE or INSERT, as appropriate: 本示例根据需要使用异常处理来执行UPDATE或INSERT：

CREATE TABLE db (a INT PRIMARY KEY, b TEXT);

CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS
$$
BEGIN
    LOOP
        -- first try to update the key
        -- note that "a" must be unique
        UPDATE db SET b = data WHERE a = key;
        IF found THEN
            RETURN;
        END IF;
        -- not there, so try to insert the key
        -- if someone else inserts the same key concurrently,
        -- we could get a unique-key failure
        BEGIN
            INSERT INTO db(a,b) VALUES (key, data);
            RETURN;
        EXCEPTION WHEN unique_violation THEN
            -- do nothing, and loop to try the UPDATE again
        END;
    END LOOP;
END;
$$
LANGUAGE plpgsql;

SELECT merge_db(1, 'david');
SELECT merge_db(1, 'dennis');

---

There's possibly an example of how to do this in bulk, using CTEs in 9.1 and above, in the hackers mailing list : 黑客邮件列表中可能有一个示例，说明如何使用9.1及更高版本中的CTE批量执行此操作：

WITH foos AS (SELECT (UNNEST(%foo[])).*)
updated as (UPDATE foo SET foo.a = foos.a ... RETURNING foo.id)
INSERT INTO foo SELECT foos.* FROM foos LEFT JOIN updated USING(id)
WHERE updated.id IS NULL;

See a_horse_with_no_name's answer for a clearer example. 有关更清楚的示例，请参见a_horse_with_no_name的答案 。

---

#6楼

For merging small sets, using the above function is fine. 对于合并小集合，使用上面的功能很好。 However, if you are merging large amounts of data, I'd suggest looking into http://mbk.projects.postgresql.org 但是，如果要合并大量数据，建议您浏览http://mbk.projects.postgresql.org

The current best practice that I'm aware of is: 我知道的当前最佳实践是：

1. COPY new/updated data into temp table (sure, or you can do INSERT if the cost is ok) 将新的/更新的数据复制到临时表中（确定，如果可以，也可以执行INSERT）
2. Acquire Lock [optional] (advisory is preferable to table locks, IMO) 获取锁[可选]（建议优先于IMO的桌面锁）
3. Merge. 合并。 (the fun part) （有趣的部分）