当前位置: 首页 > 知识库问答 >
问题:

如何在PostgreSQL中更新(合并、插入...重复更新时)?

易弘亮
2023-03-14

这里一个非常常见的问题是如何执行upsert,MySQL称之为insert...ON DUPLICATE update,标准支持将其作为merge操作的一部分。

鉴于PostgreSQL不直接支持它(在PG9.5之前),您如何做到这一点?考虑以下几点:

CREATE TABLE testtable (
    id integer PRIMARY KEY,
    somedata text NOT NULL
);

INSERT INTO testtable (id, somedata) VALUES
(1, 'fred'),
(2, 'bob');

现在假设您要“upsert”元组(2,'Joe')(3,'Alan'),那么新的表内容将是:

(1, 'fred'),
(2, 'Joe'),    -- Changed value of existing tuple
(3, 'Alan')    -- Added new tuple

这就是人们在讨论upsert时所谈论的。至关重要的是,在同一表上存在多个事务的情况下,任何方法都必须是安全的--要么使用显式锁定,要么以其他方式防止产生的竞争条件。

在Insert中,关于PostgreSQL中的重复更新?详细讨论了这个主题,但这是关于MySQL语法的替代方法,随着时间的推移,它增加了一些无关的细节。我在寻找明确的答案。

这些技术对于“如果不存在就插入,否则什么都不做”也很有用,即“插入...对重复键忽略”。

共有1个答案

鞠子轩
2023-03-14

PostgreSQL 9.5和更新版本支持insert...在冲突(key)上做更新(和在冲突(key)上做任何事情),即upsert。

与重复密钥更新的比较

快速解释。

PostgreSQL没有任何内置的upsert(或merge)功能,并且在并发使用时很难高效地执行。

本文详细讨论了这个问题。

通常,您必须在以下两个选项中进行选择:

    null

许多尝试解决此问题的方案都没有考虑回滚,因此导致更新不完整。两个交易相互竞争;其中一个成功地插入;另一个则获得重复键错误,并执行更新update块等待insert回滚或提交。当它回滚时,update条件重新检查匹配零行,因此即使update提交,它实际上也没有完成预期的upsert。您必须检查结果行计数,并在必要的地方重新尝试。

一些尝试的解决方案也没有考虑选择的种族。如果你尝试简单明了的:

-- THIS IS WRONG. DO NOT COPY IT. It's an EXAMPLE.

BEGIN;

UPDATE testtable
SET somedata = 'blah'
WHERE id = 2;

-- Remember, this is WRONG. Do NOT COPY IT.

INSERT INTO testtable (id, somedata)
SELECT 2, 'blah'
WHERE NOT EXISTS (SELECT 1 FROM testtable WHERE testtable.id = 2);

COMMIT;

然后,当两个同时运行时,有几种故障模式。一个是已经讨论过的与更新重新检查有关的问题。另一种情况是同时更新,匹配零行并继续。然后它们都执行exists测试,该测试发生在insert之前。两者都获得零行,因此都执行insert。一个失败,出现重复的密钥错误。

有时您希望进行批量更新,其中您有一个新的数据集,您希望将其合并到旧的现有数据集中。这比单独的行upsert效率高得多,在实际情况下应该优先使用。

在这种情况下,您通常遵循以下过程

>

  • 创建临时表

    提交,释放锁。

    例如,对于问题中给出的示例,使用多值insert填充临时表:

    BEGIN;
    
    CREATE TEMPORARY TABLE newvals(id integer, somedata text);
    
    INSERT INTO newvals(id, somedata) VALUES (2, 'Joe'), (3, 'Alan');
    
    LOCK TABLE testtable IN EXCLUSIVE MODE;
    
    UPDATE testtable
    SET somedata = newvals.somedata
    FROM newvals
    WHERE newvals.id = testtable.id;
    
    INSERT INTO testtable
    SELECT newvals.id, newvals.somedata
    FROM newvals
    LEFT OUTER JOIN testtable ON (testtable.id = newvals.id)
    WHERE testtable.id IS NULL;
    
    COMMIT;
    
      null

    SQL-standardmerge实际上定义的并发语义很差,不适合在不首先锁定表的情况下进行upsert。

    对于数据合并来说,这是一个非常有用的OLAP语句,但对于并发安全的upsert来说,它实际上并不是一个有用的解决方案。对于使用其他DBMSE的人来说,有很多建议可以将merge用于upserts,但这实际上是错误的。

    • 在MySQL中插入...重复键更新
    • 从MS SQL Server合并(但请参见上面关于合并问题)
    • 从Oracle合并(但请参阅上面关于合并问题)

  •  类似资料:
    • 这里一个非常常见的问题是如何执行upsert,MySQL称之为,标准支持将其作为操作的一部分。 鉴于PostgreSQL不直接支持它(在PG9.5之前),您如何做到这一点?考虑以下几点: 现在假设您要“upsert”元组,,那么新的表内容将是: 在Insert中,关于PostgreSQL中的重复更新?详细讨论了这个主题,但这是关于MySQL语法的替代方法,随着时间的推移,它增加了一些无关的细节。我

    • 问题内容: 几个月前,我从关于Stack Overflow的答案中学到了如何使用以下语法在MySQL中一次执行多个更新: 我现在已经切换到PostgreSQL,显然这是不正确的。它指的是所有正确的表,因此我认为这是使用不同关键字的问题,但是我不确定在PostgreSQL文档的哪个地方覆盖了这个问题。 为了澄清,我想插入几件事,如果它们已经存在,请对其进行更新。 问题答案: 自9.5版起的Postg

    • 问题内容: 我正在尝试使用SQLAlchemy模块(而不是SQL!)在python中编写大量upsert。 我在SQLAlchemy添加上遇到以下错误: 我有一个称为列的主键的表。 在此示例中,我已经在数据库中使用了一行。当我尝试将新对象设置为时,出现上述错误。我的印象是,如果主键已经存在,记录将得到更新。 我如何仅基于主键就可以对Flask-SQLAlchemy进行增补? 有没有简单的解决方案?

    • 问题内容: 我正在执行插入查询,其中如果已经存在唯一键,则许多列中的大多数都需要更新为新值。它是这样的: 我不确定该子句的语法应该是什么。如何从子句引用当前行? 问题答案: MySQL将假定等号之前的部分引用INSERT INTO子句中命名的列,而第二部分引用SELECT列。

    • 问题内容: MySQL有这样的东西: 据我所知,SQLite中不存在此功能,我想知道的是,是否有任何方法可以实现相同的效果而不必执行两个查询。另外,如果这不可能,那么您更喜欢什么: SELECT +(插入或更新) 或 UPDATE( 如果UPDATE失败,则 + INSERT ) 问题答案: 因为3.24.0 SQLite还支持upsert ,所以现在您可以简单地编写以下内容