How to bulk update sequence ID postgreSQL for all tables

database-sequence, postgresql, sql

Solution

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.

Problem

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?

Original source