postgresql - PostgreSQL 插入 重复更新?

  显示原文与译文双语对照的内容

几个月前我从堆栈溢出的答案中学到了如何使用以下语法在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论坛存档的一个例子做那些你可能想做, :

更新/插入, 示例 38 -2.异常

本示例使用异常处理进行适当的更新或者插入操作:


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


有可能是如何做到这一点的一个例子在 9.1及上,在黑客邮件列表:的几个小呈块状。


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 example,看到回答相关.

同时 警告:这是不安全的,如果从多个执行单元的阅读材料


在PostgreSQL中执行"更新程序"的另一种聪明方法是执行两个连续的更新/插入语句,每个语句都被设计为成功或者没有效果。


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"的行不存在时,插入才会成功。

你可以将这两个语句合并到一个字符串中,并通过从你的应用程序执行的单个SQL语句运行它们。 强烈建议在单个事务中运行它们。

它的工作方式能够很好地解决运行在隔离环境中或者在一个锁定的表格,但也容易产生竞争的情况,意味着它仍然可能失败,并出现重复键错误如果一个行被插入并发并发插入行时删除,或者它可能会立即终止,没有行。 或者高于这个 PostgreSQL 9.1上 SERIALIZABLE 事务也会在一个非常高的成本具有可靠序列化解决故障率,这意味着你将必须重试很多。 参见为什么进化器是如此复杂的,它更详细地讨论了这种情况。

这里方法也是主题,因为在 read committed 隔离中丢失了更新,除非应用程序检查受影响的行计数并验证 insert 或者 update 是否影响行标题。

使用 PostgreSQL 9.1,可以使用可写的CTE ( 公用表表达式 ) 实现:


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)

查看以下博客条目:


请注意,该解决方案将不 防止丢失更新但是不容易受到一个唯一键冲突。
查看 Ringer dba.stackexchange.com 上的Craig振铃

我在这里寻找同样的东西,但是缺少一个通用的"更新程序"函数,所以我觉得你可以通过更新并将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;
$$;

并且可能要做你最初想做的,批量"更新程序",你可以使用Tcl来拆分sql_update和环路的preformance命中将非常小的单个更新,请参见 http://archives.postgresql.org/pgsql-performance/2006-04/msg00557.php

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

没有简单的命令可以执行它。

最正确的方法是使用函数,比如文档

另一个解决方案( 虽然不是那么安全) 是进行更新,检查哪些行是更新的,并插入其余

沿着以下行的内容:


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

当然它迟早会退出( 在并发环境中),因为这里有明显的竞争条件,但通常它会工作。

这里的题目上,是一个综合article.越长越多,

如果你要插入和替换,请在上面自定义"更新程序"函数:

`


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

放置双dollar-comma以避免编译器错误非常重要

  • 检查速度。。

我个人设置了一个附加到insert语句的"规则"。 假设你有一个"dns"表,它根据per-time记录每个客户的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异常。 但是,non-terminated事务将继续并成功,你只需要重复终止的事务。

但是,如果总是有大量的插入发生,你需要在insert语句周围放置一个表锁: 共享行独占锁定将防止任何操作在目标表中插入,删除或者更新行。 但是,不更新唯一密钥的更新是安全的,因此如果你没有操作将这样做,请改用advisory咨询locks锁。

此外,复制命令不使用规则,所以如果你使用复制插入,则需要使用触发器。

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

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

这是很防弹的,平台无关的,而因为有从未超过每客户端,这只是 3有关 20设置非常低加载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

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

...