几个月前,我从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 9.5及更新版本中,你可以使用INSERT…冲突更新。

请参见文档。

MySQL插入…“ON DUPLICATE KEY UPDATE”可以直接转换为“ON CONFLICT UPDATE”。它们都不是sql标准语法,它们都是特定于数据库的扩展。没有使用MERGE是有原因的,创建新语法不是为了好玩。(MySQL的语法也有问题,这意味着它没有被直接采用)。

例如,给定的设置:

CREATE TABLE tablename (a integer primary key, b integer, c integer);
INSERT INTO tablename (a, b, c) values (1, 2, 3);

MySQL查询:

INSERT INTO tablename (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

就变成:

INSERT INTO tablename (a, b, c) values (1, 2, 10)
ON CONFLICT (a) DO UPDATE SET c = tablename.c + 1;

差异:

必须指定用于惟一性检查的列名(或惟一约束名)。这是ON CONFLICT (columnname) DO 必须使用关键字SET,就好像这是一条普通的UPDATE语句一样

它也有一些不错的功能:

You can have a WHERE clause on your UPDATE (letting you effectively turn ON CONFLICT UPDATE into ON CONFLICT IGNORE for certain values) The proposed-for-insertion values are available as the row-variable EXCLUDED, which has the same structure as the target table. You can get the original values in the table by using the table name. So in this case EXCLUDED.c will be 10 (because that's what we tried to insert) and "table".c will be 3 because that's the current value in the table. You can use either or both in the SET expressions and WHERE clause.

有关upsert的背景知识,请参阅如何upsert(合并,插入…重复更新)在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

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

我在管理帐户设置时遇到了与名称值对相同的问题。 设计标准是不同的客户端可以有不同的设置集。

我的解决方案(类似于JWP)是批量擦除和替换,在应用程序中生成合并记录。

这是非常防弹的,平台无关的,因为每个客户端从来没有超过20个设置,这只是3个相当低负载的db调用——可能是最快的方法。

更新单个行的替代方案——检查异常然后插入——或者两者的某种组合是丑陋的代码,缓慢且经常中断,因为(如上所述)非标准SQL异常处理从一个db更改到另一个db——甚至从一个版本更改到另一个版本。

 #This is pseudo-code - within the application:
 BEGIN TRANSACTION - get transaction lock
 SELECT all current name value pairs where id = $id into a hash record
 create a merge record from the current and update record
  (set intersection where shared keys in new win, and empty values in new are deleted).
 DELETE all name value pairs where id = $id
 COPY/INSERT merged records 
 END TRANSACTION

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

我用这个函数归并

CREATE OR REPLACE FUNCTION merge_tabla(key INT, data TEXT)
  RETURNS void AS
$BODY$
BEGIN
    IF EXISTS(SELECT a FROM tabla WHERE a = key)
        THEN
            UPDATE tabla SET b = data WHERE a = key;
        RETURN;
    ELSE
        INSERT INTO tabla(a,b) VALUES (key, data);
        RETURN;
    END IF;
END;
$BODY$
LANGUAGE plpgsql