How to bulk update sequence ID postgreSQL for all tables

Question

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]: Unique violation: 7 ERROR: duplicate key value violates unique constraint \"users_pkey\" DETAIL: Key (id)=(1) already exists

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?

Answer 1

Assuming that all used sequences are owned by the respective columns, e.g. through a serial or identity attribute, you can use this, to reset all (owned) sequences in the current database.

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;

The first part selects all sequences owned by a column. The second part then uses query_to_xml() to get the max value for the column associated with that sequence. And the final SELECT then applies that max value to each sequence using setval().

You might want to run that without the setval() call first to see if everything is as you need.

Answer 2

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;

I just change pg_get_serial_sequence(format('%I.%I', table_schema, table_name), column_name) as col_sequence to replace(replace(replace(column_default, '::regclass)', ''), '''', ''), 'nextval(', 'public.') as col_sequence .

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

Answer 3

You cannot update all the sequence together, as each sequence may hold different values pertaining to each table. You have to take the max value from each table and update it.

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