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

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


当前回答

根据INSERT语句的PostgreSQL文档,不支持处理ON DUPLICATE KEY情况。这部分语法是一个专有的MySQL扩展。

其他回答

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

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

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

根据INSERT语句的PostgreSQL文档,不支持处理ON DUPLICATE KEY情况。这部分语法是一个专有的MySQL扩展。

就我个人而言,我已经设置了一个附加到插入语句的“规则”。假设你有一个“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进行插入,则需要使用触发器。

类似于点赞次数最多的答案,但效果略快:

WITH upsert AS (UPDATE spider_count SET tally=1 WHERE date='today' RETURNING *)
INSERT INTO spider_count (spider, tally) SELECT 'Googlebot', 1 WHERE NOT EXISTS (SELECT * FROM upsert)

(来源:http://www.the-art-of-web.com/sql/upsert/)

当我来这里的时候,我也在寻找同样的东西,但缺乏通用的“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

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