插入，在 PostgreSQL 中重复更新？

PostgreSQL 从 9.5 版开始具有 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”会导致找到 an example of doing what you possibly want to do, in the manual：

示例 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');

在 hackers mailing list 中可能有一个如何使用 9.1 及更高版本中的 CTE 批量执行此操作的示例：

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's answer。

---

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

在 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”的行已经存在，则更新将成功，否则无效。

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

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

这在单独运行或在锁定的表上运行时效果很好，但会受到竞争条件的影响，这意味着如果同时插入一行，它可能仍会因重复键错误而失败，或者在同时删除一行时可能会因未插入行而终止. PostgreSQL 9.1 或更高版本上的 SERIALIZABLE 事务将以非常高的序列化失败率为代价可靠地处理它，这意味着您将不得不重试很多次。请参阅why is upsert so complicated，其中更详细地讨论了这种情况。

这种方法也是subject to lost updates in read committed isolation unless the application checks the affected row counts and verifies that either the insert or the update affected a row。

---

在 PostgreSQL 9.1 中，这可以使用可写 CTE (common table expression) 来实现：

WITH new_values (id, field1, field2) as (
  values 
     (1, 'A', 'X'),
     (2, 'B', 'Y'),
     (3, 'C', 'Z')

),
upsert as
( 
    update mytable m 
        set field1 = nv.field1,
            field2 = nv.field2
    FROM new_values nv
    WHERE m.id = nv.id
    RETURNING m.*
)
INSERT INTO mytable (id, field1, field2)
SELECT id, field1, field2
FROM new_values
WHERE NOT EXISTS (SELECT 1 
                  FROM upsert up 
                  WHERE up.id = new_values.id)

请参阅这些博客条目：

通过可写 CTE 进行更新插入

等待 9.1 – 可写 CTE

为什么 UPSERT 如此复杂？

请注意，此解决方案不能防止唯一密钥违规，但不易丢失更新。
请参阅follow up by Craig Ringer on dba.stackexchange.com

---

在 PostgreSQL 9.5 和更新版本中，您可以使用 INSERT ... ON CONFLICT UPDATE。

请参阅the documentation。

MySQL INSERT ... ON DUPLICATE KEY UPDATE 可以直接改写为 ON CONFLICT UPDATE。也不是 SQL 标准语法，它们都是特定于数据库的扩展。 There are good reasons MERGE wasn't used for this，创建新语法不仅仅是为了好玩。 （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 语句

它也有一些不错的功能：

您可以在 UPDATE 上有一个 WHERE 子句（让您有效地将 ON CONFLICT UPDATE 转换为 ON CONFLICT IGNORE 对于某些值）

建议插入值可用作行变量 EXCLUDED，其结构与目标表相同。您可以通过使用表名来获取表中的原始值。所以在这种情况下，EXCLUDED.c 将是 10（因为这是我们试图插入的），“table”.c 将是 3，因为这是表中的当前值。您可以在 SET 表达式和 WHERE 子句中使用其中一个或两个。

有关 upsert 的背景信息，请参阅 How to UPSERT (MERGE, INSERT ... ON DUPLICATE UPDATE) in PostgreSQL?

---

当我来到这里时，我正在寻找同样的东西，但是缺少通用的“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

最高成本是从您的代码中执行查询，在数据库端执行成本要小得多

---

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

最正确的方法是使用函数，例如 docs 中的函数。

另一种解决方案（虽然不是那么安全）是通过返回进行更新，检查哪些行是更新的，然后插入其余的行

类似于以下内容：

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

当然它迟早会退出（在并发环境中），因为这里有明确的竞争条件，但通常它会起作用。

这是一个longer and more comprehensive article on the topic。

---

就个人而言，我已经设置了一个附加到插入语句的“规则”。假设您有一个“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

---

如果您想插入和替换，我在上面自定义了“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... $$)

放置双美元逗号以避免编译器错误很重要

检查速度...

---

类似于最喜欢的答案，但工作速度稍快：

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/）

---

根据 PostgreSQL documentation of the INSERT statement，不支持处理 ON DUPLICATE KEY 情况。这部分语法是专有的 MySQL 扩展。

---

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

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

这是非常安全的，独立于平台的，并且由于每个客户端的设置永远不会超过 20 个，因此这只是 3 个负载相当低的数据库调用 - 可能是最快的方法。

更新单个行的替代方法 - 检查异常然后插入 - 或某种组合是可怕的代码，缓慢且经常中断，因为（如上所述）非标准 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

---

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

---

对于合并小集合，使用上述函数就可以了。但是，如果您要合并大量数据，我建议您查看 http://mbk.projects.postgresql.org

我知道的当前最佳实践是：

将新/更新的数据复制到临时表中（当然，或者如果成本合适，您可以执行插入）获取锁 [可选]（建议优于表锁，IMO）合并。 （有趣的部分）

---

编辑：这没有按预期工作。与接受的答案不同，当两个进程同时重复调用 upsert_foo 时，这会产生唯一的密钥冲突。

尤里卡！我想出了一种在一个查询中执行此操作的方法：使用 UPDATE ... RETURNING 测试是否有任何行受到影响：

CREATE TABLE foo (k INT PRIMARY KEY, v TEXT);

CREATE FUNCTION update_foo(k INT, v TEXT)
RETURNS SETOF INT AS $$
    UPDATE foo SET v = $2 WHERE k = $1 RETURNING $1
$$ LANGUAGE sql;

CREATE FUNCTION upsert_foo(k INT, v TEXT)
RETURNS VOID AS $$
    INSERT INTO foo
        SELECT $1, $2
        WHERE NOT EXISTS (SELECT update_foo($1, $2))
$$ LANGUAGE sql;

UPDATE 必须在单独的过程中完成，因为不幸的是，这是一个语法错误：

... WHERE NOT EXISTS (UPDATE ...)

现在它可以按需要工作：

SELECT upsert_foo(1, 'hi');
SELECT upsert_foo(1, 'bye');
SELECT upsert_foo(3, 'hi');
SELECT upsert_foo(3, 'bye');

---

UPDATE 将返回修改的行数。如果您使用 JDBC (Java)，则可以检查该值是否为 0，如果没有行受到影响，则改为触发 INSERT。如果您使用其他编程语言，可能仍然可以获得修改的行数，请查看文档。

这可能不那么优雅，但您有更简单的 SQL，在调用代码中使用起来更简单。不同的是，如果您在 PL/PSQL 中编写十行脚本，您可能应该单独为它进行一种或另一种类型的单元测试。

---