Postgres：更新所有表的主键顺序 [英] Postgres: Update primary key sequence for all tables

问题描述

我已将生产中的所有数据手动导入到开发服务器中，但出现此错误。我还阅读了此处，此问题已解决，但仅限于单桌。我已经导入了大约10多个表格及其数据。这是错误：

I've manually imported all the data from production to my development server but I'm having this error. I've also read here that fixes this issue but is only limited to a single table. I've imported around 10+ tables along with their data. This is the error:

PG::UniqueViolation: ERROR: duplicate key value violates unique constraint "influences_pkey" DETAIL: Key (id)=(1) already exists. : INSERT INTO "influences" ("name", "created_at", "updated_at") VALUES ($1, $2, $3) RETURNING "id"

推荐答案

这是plpgsql重置所有序列（在pgadmin或psql或任何其他客户端中运行）：

here is plpgsql to reset all sequences (run in pgadmin or psql or any other client):

do 
$$
declare
 _r record;
 _i bigint;
 _m bigint;
begin
  for _r in (
    SELECT relname,nspname,d.refobjid::regclass, a.attname, refobjid
    FROM   pg_depend    d
    JOIN   pg_attribute a ON a.attrelid = d.refobjid AND a.attnum = d.refobjsubid
    JOIN pg_class r on r.oid = objid
    JOIN pg_namespace n on n.oid = relnamespace
    WHERE  d.refobjsubid > 0 and  relkind = 'S'
   ) loop
    execute format('select last_value from %I.%I',_r.nspname,_r.relname) into _i;
    execute format('select max(%I) from %s',_r.attname,_r.refobjid) into _m;
    if coalesce(_m,0) > _i then
      raise info '%',concat('changed: ',_r.nspname,'.',_r.relname,' from:',_i,' to:',_m); 
      execute format('alter sequence %I.%I restart with %s',_r.nspname,_r.relname,_m+1);
    end if;
  end loop;

end;
$$
;

或使用在> Postgres的主键序列不同步时如何重置？

这篇关于Postgres：更新所有表的主键顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！