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?

Girardi

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.

Milford

I tested on my original database, seen worked, it's show list of my sequence value and new value, but I tried on the server that I imported it's showing 0 rows, why?? – Girardi

I think it's caused by pg_get_serial_sequence is not defined after imported in new server, I run 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 return zero in new server but it's return with data in original server – Girardi

pg_get_serial_sequence is a built-in function it can't "not defined". If that query doesn't return anything, you lost the ownership between the column and the sequence for some reason during your "import" – Milford

I see,, I am still workaround using query column_default ILIKE 'nextval(%' , I want to change your col_sequence from pg_get_serial_sequence function to column_default. Do you now how to extract col_sequence from column_default with the value is "nextval('users_id_seq'::regclass)" . So I need to get users_id_seq from nextval('users_id_seq'::regclass) – Girardi

You should rather fix your export/import then hacking around with the default value. If you really need that, then please ask a new question. – Milford

I did export/import using my SQL client(tableplus), so I think nothing is wrong, but it's okay, thank you. your answer is helpful. I will try first for challenging my self, if I stuck I will create a new question. – Girardi

I'd say there is something wrong there, apparently TablePlus does not generate the necessary owned by for the sequences. Use pg_dump and compare the output – Milford

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.

Girardi

If you need to use the column_default instead of pg_get_serial_sequence() this means your columns are neither defined as serial nor as identity. I highly recommend you investigate why this isn't the case, because you should really be using that. – Milford

This answer actually works from lots of others I tried, thanks! – Stormi

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_;

Annecy