在PostgreSQL中重复更新时插入？-Java 学习之路

547

几个月前，我从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文档中的哪个被覆盖 .

为了澄清，我想插入几个东西，如果它们已经存在则更新它们 .

16 回答

383

自9.5版以来PostgreSQL具有UPSERT语法， ON CONFLICT clause. 具有以下语法（类似于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');

可能有一个如何在_643922中使用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 .

回复于 2024-05-10T22:15:23+08:00

412
Warning: this is not safe if executed from multiple sessions at the same time （见下面的警告） .

在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语句来运行它们 . 强烈建议在单个事务中一起运行它们 .

这在隔离或锁定表上运行时非常有效，但是受竞争条件的影响，这意味着如果同时插入行，它可能仍会因重复键错误而失败，或者可能会在同时删除行时终止而不插入行 . 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 .
回复于 2024-05-10T22:15:23+08:00

219

使用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)

查看这些博客条目：

请注意，此解决方案会阻止唯一的密钥冲突，但它不容易丢失更新 .
见follow up by Craig Ringer on dba.stackexchange.com

回复于 2024-05-10T22:15:23+08:00

104
在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，一种新的语法isn 't created just for fun. (MySQL' s语法也有问题意味着它没有被直接采用） .

例如给定设置：
```
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?
回复于 2024-05-10T22:15:23+08:00
8
当我来到这里的时候我正在找同样的东西，但缺乏一个通用的“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

最高的成本是从您的代码执行查询，在数据库端执行成本要小得多
回复于 2024-05-10T22:15:23+08:00
4
没有简单的命令可以做到这一点 .

最正确的方法是使用函数，如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 .
回复于 2024-05-10T22:15:23+08:00
3
就个人而言，我已经设置了一个附加到insert语句的“规则” . 假设您有一个记录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异常 . 但是，未终止的事务将继续并成功，您只需重复已终止的事务 .

但是，如果一直有大量的插入事件发生，您将需要在insert语句周围放置一个表锁：SHARE ROW EXCLUSIVE锁定将阻止任何可以在目标表中插入，删除或更新行的操作 . 但是，不更新唯一键的更新是安全的，因此如果您不执行此操作，请改用咨询锁 .

此外，COPY命令不使用RULES，因此如果您使用COPY进行插入，则需要使用触发器 .
回复于 2024-05-10T22:15:23+08:00

我自定义上面的“upsert”函数，如果你想INSERT AND REPLACE：

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... $$)

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

检查速度......

回复于 2024-05-10T22:15:23+08:00

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

我的解决方案与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
```
回复于 2024-05-10T22:15:23+08:00

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

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

回复于 2024-05-10T22:15:23+08:00

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

回复于 2024-05-10T22:15:23+08:00

5

UPDATE将返回已修改行的数量 . 如果使用JDBC（Java），则可以对0检查此值，如果没有影响行，则激活INSERT . 如果您使用其他编程语言，可能仍然可以获得修改行的数量，请查看文档 .

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

回复于 2024-05-10T22:15:23+08:00

我用这个函数合并

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

回复于 2024-05-10T22:15:23+08:00

7

根据PostgreSQL documentation of the INSERT statement，不支持处理 ON DUPLICATE KEY 案例 . 该部分语法是专有的MySQL扩展 .

回复于 2024-05-10T22:15:23+08:00

Edit: 这不能按预期工作 . 与接受的答案不同，当两个进程同时重复调用 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');

回复于 2024-05-10T22:15:23+08:00

3
对于合并小集合，使用上述函数很好 . 但是，如果要合并大量数据，我建议调查http://mbk.projects.postgresql.org

我所知道的当前最佳实践是：
- 将新数据/更新数据复制到临时表中（当然，如果成本合适，您可以执行INSERT）
- 获取锁[可选]（建议优于表锁，IMO）
- 合并 . （有趣的部分）
回复于 2024-05-10T22:15:23+08:00

在PostgreSQL中重复更新时插入？

16 回答

相关问题