在 PostgreSQL 中插入时如果重复则更新应该怎么做？

23 浏览
0 Comments

在 PostgreSQL 中插入时如果重复则更新应该怎么做？

几个月前，我从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文档的哪里涵盖了这个问题。为了澄清，我想插入几个东西，如果它们已经存在，则更新它们。

admin 更改状态以发布 2023年5月20日
0

2 答案

0 Comments

警告：如果同时从多个会话执行，这是不安全的（请参见下面的注意事项）。

在PostgreSQL中执行"UPSERT"的另一种聪明方法是进行两个连续的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);

如果具有"id=3"的行已经存在，则UPDATE将成功，否则不会产生影响。

仅当"id=3"的行不存在时，INSERT才会成功。

您可以将这两个组合成单个字符串，并使用单个SQL语句从应用程序中运行它们。强烈建议在单个事务中同时运行它们。

当在隔离环境或锁定表上运行时，它非常有效，但受到竞争条件的影响，这意味着如果同时插入一行，它可能仍会因重复键错误而失败，或者在同时删除行时可能终止而未插入行。PostgreSQL 9.1或更高版本上的可串行化事务可以可靠地处理它，但代价是非常高的串行化失败率，这意味着您将不得不重试很多次。有关更多详细信息，请参见upsert之所以如此复杂，其中讨论了这种情况。

此方法在read committed隔离级别下也存在丢失更新的风险，除非应用程序检查受影响的行数，并验证insert或update是否影响了行数。

0
0 Comments

自从版本9.5起PostgreSQL就有了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及以上版本中，通过使用CTEs大量执行此操作的示例可能会在黑客邮件列表中找到：

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的回答以获得更清晰的示例。

0