Postgres：更新所有表的主键序列(Postgres: Update primary key sequence for all tables)

我已经手动将所有数据从生产导入到我的开发服务器，但是我遇到了这个错误。我也在这里阅读了解决这个问题，但仅限于一张表。我已经输入了大约10+个表格以及他们的数据。这是错误：

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"

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或任何其他客户端运行）：

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的主键序列时，它不同步？

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

or use any other solution proposed at How to reset postgres' primary key sequence when it falls out of sync?

更多推荐