几个月前,我从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文档中这是被覆盖的。

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


当前回答

PostgreSQL >= v15

关于这个话题的大新闻是在PostgreSQL v15中,可以使用MERGE命令。事实上,这个期待已久的特性被列为v15版本的第一个改进。

这类似于INSERT…ON冲突,但更面向批处理。它具有强大的WHEN MATCHED vs WHEN NOT MATCHED结构,可以在这种情况下执行INSERT、UPDATE或DELETE操作。

它不仅简化了批量更改,而且甚至比传统的UPSERT和INSERT增加了更多的控制…在冲突

看看这个非常完整的样本从官方页面:

MERGE INTO wines w
USING wine_stock_changes s
ON s.winename = w.winename
WHEN NOT MATCHED AND s.stock_delta > 0 THEN
  INSERT VALUES(s.winename, s.stock_delta)
WHEN MATCHED AND w.stock + s.stock_delta > 0 THEN
  UPDATE SET stock = w.stock + s.stock_delta
WHEN MATCHED THEN
  DELETE;

PostgreSQL v9, v10, v11, v12, v13, v14

如果版本低于v15而高于v9.5,可能最好的选择是使用UPSERT语法,带有ON CONFLICT子句

其他回答

当我来这里的时候,我也在寻找同样的东西,但缺乏通用的“upsert”函数让我有点困扰,所以我认为你可以通过更新和插入sql作为该函数的参数形式手册

它看起来是这样的:

CREATE FUNCTION upsert (sql_update TEXT, sql_insert TEXT)
    RETURNS VOID
    LANGUAGE plpgsql
AS $$
BEGIN
    LOOP
        -- first try to update
        EXECUTE sql_update;
        -- check if the row is found
        IF FOUND THEN
            RETURN;
        END IF;
        -- not found so insert the row
        BEGIN
            EXECUTE sql_insert;
            RETURN;
            EXCEPTION WHEN unique_violation THEN
                -- do nothing and loop
        END;
    END LOOP;
END;
$$;

也许要做你最初想做的事情,批处理“upsert”,你可以使用Tcl分割sql_update并循环各个更新,性能的影响将非常小,参见http://archives.postgresql.org/pgsql-performance/2006-04/msg00557.php

最高的成本是从您的代码执行查询,在数据库方面的执行成本要小得多

对于合并小集合,可以使用上面的函数。但是,如果您正在合并大量数据,我建议您查看http://mbk.projects.postgresql.org

目前我所知道的最佳实践是:

复制新的/更新的数据到临时表(当然,或者你可以做INSERT,如果成本是ok) 获得锁[可选](建议优先于表锁,IMO) 合并。(有趣的部分)

就我个人而言,我已经设置了一个附加到插入语句的“规则”。假设你有一个“dns”表,记录每个客户每次的dns点击量:

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

您希望能够重新插入具有更新值的行,或者在这些行不存在时创建它们。输入customer_id和时间。就像这样:

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

更新:如果同时进行插入,则有可能失败,因为它将生成unique_violation异常。但是,未终止的事务将继续并成功,您只需要重复已终止的事务。

然而,如果一直都有大量的插入发生,你会想在插入语句周围放一个表锁:SHARE ROW EXCLUSIVE锁将阻止任何可能在目标表中插入、删除或更新行的操作。但是,不更新唯一密钥的更新是安全的,因此如果没有操作将这样做,则使用建议锁。

此外,COPY命令不使用RULES,因此如果使用COPY进行插入,则需要使用触发器。

没有简单的命令可以做到这一点。

最正确的方法是使用函数,就像docs中的函数一样。

另一种解决方案(尽管不是那么安全)是在执行update的同时返回,检查哪些行是更新的,然后插入其余的行

大致如下:

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;

假设返回id:2:

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

当然,(在并发环境中)它迟早会崩溃,因为这里有明确的竞态条件,但通常它会工作。

这里有一篇关于这个主题的更长的、更全面的文章。

CREATE OR REPLACE FUNCTION save_user(_id integer, _name character varying)
  RETURNS boolean AS
$BODY$
BEGIN
    UPDATE users SET name = _name WHERE id = _id;
    IF FOUND THEN
        RETURN true;
    END IF;
    BEGIN
        INSERT INTO users (id, name) VALUES (_id, _name);
    EXCEPTION WHEN OTHERS THEN
            UPDATE users SET name = _name WHERE id = _id;
        END;
    RETURN TRUE;
END;

$BODY$
  LANGUAGE plpgsql VOLATILE STRICT