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

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


当前回答

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

最正确的方法是使用函数,就像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

当我来这里的时候,我也在寻找同样的东西,但缺乏通用的“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) 合并。(有趣的部分)

我自定义“upsert”函数上面,如果你想插入和替换:

 CREATE OR REPLACE FUNCTION upsert(sql_insert text, sql_update text)

 RETURNS void AS
 $BODY$
 BEGIN
    -- first try to insert and after to update. Note : insert has pk and update not...

    EXECUTE sql_insert;
    RETURN;
    EXCEPTION WHEN unique_violation THEN
    EXECUTE sql_update; 
    IF FOUND THEN 
        RETURN; 
    END IF;
 END;
 $BODY$
 LANGUAGE plpgsql VOLATILE
 COST 100;
 ALTER FUNCTION upsert(text, text)
 OWNER TO postgres;`

在执行之后,像这样做:

SELECT upsert($$INSERT INTO ...$$,$$UPDATE... $$)

重要的是放双元逗号,以避免编译错误

检查速度…

警告:如果同时从多个会话执行,这是不安全的(参见下面的警告)。


在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语句同时运行它们。强烈建议在一个事务中同时运行它们。

This works very well when run in isolation or on a locked table, but is subject to race conditions that mean it might still fail with duplicate key error if a row is inserted concurrently, or might terminate with no row inserted when a row is deleted concurrently. A SERIALIZABLE transaction on PostgreSQL 9.1 or higher will handle it reliably at the cost of a very high serialization failure rate, meaning you'll have to retry a lot. See why is upsert so complicated, which discusses this case in more detail.

这种方法在读提交隔离中还可能导致更新丢失,除非应用程序检查受影响的行数,并验证插入或更新是否影响了行。