当前位置: 首页 > 面试题库 >

使用ON CONFLICT从INSERT返回行,而无需更新

童铭晨
2023-03-14
问题内容

我遇到的情况是,我经常需要从具有唯一约束的表中获取行,如果不存在,则创建它并返回。例如,我的表可能是:

CREATE TABLE names(
    id SERIAL PRIMARY KEY,
    name TEXT,
    CONSTRAINT names_name_key UNIQUE (name)
);

它包含:

id | name
 1 | bob 
 2 | alice

然后,我想:

 INSERT INTO names(name) VALUES ('bob')
 ON CONFLICT DO NOTHING RETURNING id;

也许:

 INSERT INTO names(name) VALUES ('bob')
 ON CONFLICT (name) DO NOTHING RETURNING id

并返回bob的id
1。但是,RETURNING仅返回插入或更新的行。因此,在上面的示例中,它不会返回任何内容。为了使它发挥所需的功能,我实际上需要:

INSERT INTO names(name) VALUES ('bob') 
ON CONFLICT ON CONSTRAINT names_name_key DO UPDATE
SET name = 'bob'
RETURNING id;

这似乎很麻烦。我想我的问题是:

  1. 不允许(我的)期望行为的原因是什么?

  2. 有没有更优雅的方法可以做到这一点?


问题答案:

这是反复出现的问题 SELECT or INSERT ,与UPSERT相关(但与UPSERT不同)。Postgres
9.5中的新UPSERT功能仍然起作用。

WITH ins AS (
   INSERT INTO names(name)
   VALUES ('bob')
   ON     CONFLICT ON CONSTRAINT names_name_key DO UPDATE
   SET    name = NULL
   WHERE  FALSE      -- never executed, but locks the row
   RETURNING id
   )
SELECT id FROM ins
UNION  ALL
SELECT id FROM names
WHERE  name = 'bob'  -- only executed if no INSERT
LIMIT  1;

这样,您实际上并不需要编写新的行版本。

我假设您知道在PostgresUPDATE中,由于其MVCC模型,每个人都会写入该行的新版本-
即使name设置为与以前相同的值。这将使操作更加昂贵,在某些情况下增加可能的并发问题/锁争用,并另外膨胀表。

但是 ,在 竞赛条件下 仍然存在一个 极小的情况 。并发事务可能添加了冲突的行,该行在同一条语句中尚不可见。然后INSERT
SELECT空了出来。

单行UPSERT的正确解决方案:

  • SELECT或INSERT函数是否易于出现竞争状况?

批量UPSERT的常规解决方案:

  • 如何在PostgreSQL中结合使用ON CONFLICT和RETURNING?

没有并发写入负载

如果不可能进行并发写入(来自不同的会话),则不需要锁定行,可以简化以下操作:

WITH ins AS (
   INSERT INTO names(name)
   VALUES ('bob')
   ON     CONFLICT ON CONSTRAINT names_name_key DO NOTHING  -- no lock needed
   RETURNING id
   )
SELECT id FROM ins
UNION  ALL
SELECT id FROM names
WHERE  name = 'bob'  -- only executed if no INSERT
LIMIT  1;


 类似资料:
  • 问题内容: 我想知道如何更改URL而不重定向,就像在我们的网站http://dekho.com.pk/ads-in- lahore 上那样, 当我们单击选项卡时url会更改,但是页面完全重新加载。在上还有其他问题,表明这是不可能的,但我想知道上述网站是如何实现的。谢谢 问题答案: 用途:

  • 我一直在尝试使用插入。。。在MySQL中返回基于DSL的表定义(我没有使用代码生成),返回的记录总是空的。基于阅读,我需要在表定义中指定标识列,但我不知道如何指定! 字段“id”是数据库中的自动增量主键,但recordKey始终为空。

  • 问题内容: 我在node.js中编写一个函数来查询PostgreSQL表。 如果该行存在,我想从该行返回id列。 如果不存在,我想将其插入并返回ID()。 我一直在尝试和语句的变体,但似乎无法使其正常工作。 问题答案: 我建议在数据库端进行检查,然后将ID返回给nodejs。 例子: 而不是在Node.js端(在此示例中,我使用的是node-postgres):

  • 好吧,问题不是关于如何返回ID,而是更像是如何跳过它...我有一种情况,我使用last_insert_id()获取最后插入行的id,但如果没有插入行,我想得到一个零。问题如下: < li >我插入...更新一些记录:由于这些行是新插入的,所以返回所有id < li >然后我重复该过程:这次不返回id。耶!这正是我想要的 < li >我更改了一个列值并重复该过程:但现在返回的是我更改了列值的行的ID

  • 问题内容: 所以我有一个旧数据库,正在迁移到新数据库。新版本的架构略有不同,但大多兼容。另外,我想从零开始重新编号所有表。 目前,我一直在使用编写的工具手动检索旧记录,将其插入新数据库,并更新旧数据库中的v2 ID字段以在新数据库中显示其对应的ID位置。 例如,我从MV5.Posts中选择并将其插入MV6.Posts。插入后,我在MV6.Posts中检索新行的ID,并在旧的MV5.Posts.MV

  • 我已经在我的应用程序中实现了MVVM架构。我有我的活动、视图模型、存储库、DAO和数据库类。数据库保存包含不同列表的对象,我想在这些列表之间切换,并让我的RecycraView显示当前选定的列表。我的理解是SQLite中的行ID从1开始,但是我的存储库中的插入方法(在我的DAO中调用@插入方法)总是从0开始。出于测试目的,我使用LiveData获取数据库中的所有对象,当我记录它们的ID时,它们正确