Skip to content
Advertisement

How to bulk update sequence ID postgreSQL for all tables

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:

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?

Advertisement

Answer

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.

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.

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement