插入，在 PostgreSQL 中重复更新？

小编典典

插入，在 PostgreSQL 中重复更新？

all

几个月前，我从 Stack Overflow 上的一个答案中了解到如何使用以下语法在 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);

我现在已经切换到 PostgreSQL，显然这是不正确的。它指的是所有正确的表，所以我认为这是使用不同关键字的问题，但我不确定 PostgreSQL
文档中的哪个位置涵盖了这一点。

为了澄清，我想插入一些东西，如果它们已经存在来更新它们。

阅读 159

2022-03-03

共1个答案

小编典典

PostgreSQL 从 9.5
版开始具有UPSERT语法，带有 ON
CONFLICT 子句。使用以下语法（类似于 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：

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

在黑客邮件列表中，可能有一个如何使用 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;

有关更清晰的示例，请参见a_horse_with_no_name
的答案。

2022-03-03