sql - 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.