插入，在PostgreSQL中重复更新吗? [英] Insert, on duplicate update in PostgreSQL?

问题描述

几个月前，我从Stack Overflow的答案中学到了如何使用以下语法在MySQL中一次执行多个更新:

Several months ago I learned from an answer on Stack Overflow how to perform multiple updates at once in MySQL using the following syntax:

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);

我现在已经切换到PostgreSQL，显然这是不正确的.它指的是所有正确的表，因此我认为这是使用不同关键字的问题，但是我不确定在PostgreSQL文档中的覆盖范围是什么.

I've now switched over to PostgreSQL and apparently this is not correct. 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.

为澄清起见，我想插入几件事，如果它们已经存在，请对其进行更新.

To clarify, I want to insert several things and if they already exist to update them.

推荐答案

PostgreSQL自9.5版以来具有 UPSERT 语法，与 ON CONFLICT 子句.，语法如下(类似于MySQL)

PostgreSQL since version 9.5 has UPSERT syntax, with ON CONFLICT clause. with the following syntax (similar to 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;

---

在Postgresql的电子邮件组归档中搜索"upsert"会导致找到在手册中做您想做的事的示例:

示例38-2. UPDATE/INSERT的例外

此示例根据需要使用异常处理来执行UPDATE或INSERT:

This example uses exception handling to perform either UPDATE or INSERT, as appropriate:

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');

---

在有关更清楚的示例，请参见 a_horse_with_no_name的答案.

See a_horse_with_no_name's answer for a clearer example.

这篇关于插入，在PostgreSQL中重复更新吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！