如何批量更新所有表的序列 ID postgreSQL [英] How to bulk update sequence ID postgreSQL for all tables

问题描述

我使用 TablePlus(SQL 客户端)将 Postgres SQL 文件导入到我的服务器，但在插入新行后出现如下错误:

I imported the Postgres SQL file to my server using TablePlus(SQL client), but after I insert new row I got error like this:

SQLSTATE[23505]:唯一违规:7 错误:重复键值违反唯一约束 \"users_pkey\"详细信息:密钥 (id)=(1) 已存在

SQLSTATE[23505]: Unique violation: 7 ERROR: duplicate key value violates unique constraint \"users_pkey\" DETAIL: Key (id)=(1) already exists

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

I know it caused by sequence value is 0 and need to be updated by code below:

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

但是如果我必须一一写入所有表序列(可能是数百个序列)，则需要很多时间.那么如何一次更新所有序列呢?

But it needs so much time if I must write to all table sequences(maybe hundreds of sequences) one by one. So how to update all sequences at once?

推荐答案

as @a_horse_with_no_name answer 在我的情况下不起作用(也许 SQL 文件有问题)，我修改了如下所示的查询，适用于我的情况.

as @a_horse_with_no_name answer doesn't work in my case (maybe something is wrong with the SQL file), I modified the query like below that works in my case.

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) 改为 col_sequence 到 replace(replace(replace(column_default, '::regclass))', ''), '''', ''), 'nextval(', 'public.') as col_sequence .

也许我的查询不太好，我应该使用正则表达式而不是多次替换.但就我而言，它 100% 有效.

Maybe my query is not too good, I should use regex instead of multiple replace. but it's work 100% in my case.

这篇关于如何批量更新所有表的序列 ID postgreSQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！