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

Question

我使用TablePlus（SQL客户端）将Postgres SQL文件导入到服务器，但是在插入新行后出现如下错误：

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

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

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

但是，如果我必须一一写入所有表序列（可能是数百个序列），则需要花费大量时间。那么如何一次更新所有序列呢？

Answer 1

由于@a_horse_with_no_name答案在我的情况下不起作用（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(format('%I.%I', table_schema, table_name), column_name) as col_sequence为replace(replace(replace(column_default, '::regclass)', ''), '''', ''), 'nextval(', 'public.') as col_sequence。

也许我的查询不是很好，我应该使用正则表达式而不是多个替换。但就我而言，这是100％可行的。