PG导入数据 duplicate key value violates unique constraint解决

PG的staging环境导入测试数据，出现 duplicate key value violates unique constraint

表: userextrainfo

最大id

SELECT max(id) from userextrainfo;
1300

下一个自增id

SELECT nextval('userextrainfo_id_seq');

需要重新reset下自增id

select setval('userextrainfo_id_seq',1301);

one sql

SELECT setval('tablename_id_seq', (SELECT MAX(id) FROM tablename)+1)

多个表批量更新sql

来源 https://stackoverflow.com/que...

do
$block$
declare 
  r        record;
  stmt     text;
  max_id   integer;
begin
  for r in (
              select *
              from (
                select table_schema, 
                       table_name, 
                       column_name, 
                       pg_get_serial_sequence(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
        ) 
  loop
    stmt := 'select coalesce(max('||r.column_name||'), 0) + 1 from '||r.table_schema||'.'||r.table_name;
    execute stmt into max_id;
    raise notice 'Next ID for %.%.% is %', r.table_schema, r.table_name, r.column_name, max_id;
    perform setval(r.col_sequence, max_id); 
  end loop;
end;
$block$