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

Question

我导入PostgresSQL文件到我的服务器使用TablePlus（SQL客户端），但我插入新行后，我得到了这样的错误：

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

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

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

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

Answer 1

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

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

Answer 2

由于在我的案例中，@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%有效的。

Answer 3

假设所有使用的序列都属于相应的列，例如通过serial或identity属性，可以使用此属性重置当前数据库中的所有（拥有的）序列。

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（）的情况下先运行它，看看是否一切都符合您的需要。