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