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

如何批量更新所有表的序列ID postgreSQL

施阳夏
2023-03-14

我导入PostgresSQL文件到我的服务器使用TablePlus(SQL客户端),但我插入新行后,我得到了这样的错误:

SQLSTATE[23505]:唯一冲突:7错误:重复键值违反唯一约束\“users\u pkey\”详细信息:键(id)=(1)已存在

我知道它是由序列值为0引起的,需要通过下面的代码进行更新:

SELECT setval(_sequence_name_, max(id)) FROM _table_name_;

但是如果我必须一个接一个地写所有的表序列(可能是数百个序列),那就需要很多时间。那么如何一次更新所有序列呢?

共有3个答案

俞新翰
2023-03-14

您不能一起更新所有序列,因为每个序列可能包含与每个表相关的不同值。您必须从每个表中获取最大值并更新它。

SELECT setval(_sequence_name_, max(id)) FROM _table_name_;
邹晟睿
2023-03-14

由于在我的案例中,@a_horse_with_no_name answer不起作用(可能是SQL文件出了问题),我修改了在我的案例中起作用的查询,如下所示。

with sequences as (
  select *
  from (
    select table_schema,
           table_name,
           column_name,
           replace(replace(replace(column_default, '::regclass)', ''), '''', ''), 'nextval(', 'public.') as col_sequence
    from information_schema.columns
    where table_schema not in ('pg_catalog', 'information_schema') and column_default ILIKE 'nextval(%'
  ) t
  where col_sequence is not null
), maxvals as (
  select table_schema, table_name, column_name, col_sequence,
          (xpath('/row/max/text()',
             query_to_xml(format('select max(%I) from %I.%I', column_name, table_schema, table_name), true, true, ''))
          )[1]::text::bigint as max_val
  from sequences
) 
select table_schema, 
       table_name, 
       column_name, 
       col_sequence,
       coalesce(max_val, 0) as max_val,
       setval(col_sequence, coalesce(max_val, 1)) --<< this will change the sequence
from maxvals;

我只是更改pg_get_serial_sequence(格式('%I.%I',table_schema,table_name),column_name)col_sequence替换(替换(替换(column_default,::regclass)', ''), '''', ''), 'nextval(','Public.')col_sequence.

也许我的查询不太好,我应该使用regex而不是multiplereplace。但对我来说,这是100%有效的。

宋鸿德
2023-03-14

假设所有使用的序列都属于相应的列,例如通过serialidentity属性,可以使用此属性重置当前数据库中的所有(拥有的)序列。

with sequences as (
  select *
  from (
    select table_schema,
           table_name,
           column_name,
           pg_get_serial_sequence(format('%I.%I', table_schema, table_name), column_name) as col_sequence
    from information_schema.columns
    where table_schema not in ('pg_catalog', 'information_schema')
  ) t
  where col_sequence is not null
), maxvals as (
  select table_schema, table_name, column_name, col_sequence,
          (xpath('/row/max/text()',
             query_to_xml(format('select max(%I) from %I.%I', column_name, table_schema, table_name), true, true, ''))
          )[1]::text::bigint as max_val
  from sequences
) 
select table_schema, 
       table_name, 
       column_name, 
       col_sequence,
       coalesce(max_val, 0) as max_val,
       setval(col_sequence, coalesce(max_val, 1)) --<< this will change the sequence
from maxvals;

第一部分选择列拥有的所有序列。然后,第二部分使用query_to_xml()获取与该序列关联的列的最大值。最后的SELECT然后使用setval()将最大值应用于每个序列。

您可能希望在没有调用setval()的情况下先运行它,看看是否一切都符合您的需要。

 类似资料:
  • 问题内容: 我使用TablePlus(SQL客户端)将Postgres SQL文件导入到服务器,但是在插入新行后出现如下错误: SQLSTATE [23505]:唯一冲突:7错误:重复的键值违反了唯一约束\“ users_pkey \”详细信息:密钥(id)=(1)已存在 我知道它是由序列值0引起的,需要通过以下代码进行更新: 但是,如果我必须一一写入所有表序列(可能是数百个序列),则需要花费大量

  • 我正在使用PostgreSQL,并有一个名为的表,其中包含500万行和以下结构: 另一个表具有相同的行数和以下结构: 我需要 的值设置。如果我发出这样的声明: 这需要几个小时,而且我需要经常做这个手术。这两个表按id排序,id是惟一的,并且这两个表都有id。对我来说,用< code>table_flag中的值设置< code>table_all中的< code>some_flag只是执行从< co

  • 问题内容: 我正在尝试更新所有以’agg%’和column_name=’%userid%’之类的字符串开头的表…但是即使我能够找到选择具有特定列的所有表的选项,我也看不到在线的此类示例名称和表名称我需要执行相同的操作来更新这些表,如下所示: 帮助将不胜感激。 谢谢。 问题答案: 获取您条件的更新查询 执行

  • 我正在尝试在现有列表中添加一个新项目,但无法添加。当我调试时,它显示返回为true,但最终新项没有添加到列表中。 我的示例代码如下:Employee employee1=new Employee(5001,“BOB”,financeDept.getDepartment_name());employee2=新员工(5002,“SAM”,FinanceDepartment.getDepartment_

  • 问题内容: 我想用Django更新表格-原始SQL中的内容如下: 我的第一个结果是这样的-但这很讨厌,不是吗? 有没有更优雅的方式? 问题答案: UPD Django 2.2版本现在具有bulk_update。 请参阅以下django文档部分 一次更新多个对象 简而言之,你应该可以使用: 你还可以使用F对象来执行诸如增加行数之类的操作: 请参阅文档:https : //docs.djangopro

  • 我基本上是在中更新一个值,并使用将其发送到片段。所有片段都应该在上显示相同的值。 我的问题是我的应用程序有3个片段页面,只有中间的页面(Fragment_B)在更新... 为什么所有的片段不同时更新?该值从不显示在Fragment_A和fragment_c上。 应该更新所有的片段,但它只更新片段B。但是我试图调试这个问题,并创建了和SetText,它适用于每个片段。我不知道为什么会这样。有人能帮帮