丑陋的黑客修复它使用一些贝壳魔法，不是一个伟大的解决方案，但可能会激励其他人有类似的问题:)

pg_dump -s <DATABASE> | grep 'CREATE TABLE' | awk '{print "SELECT setval(#" $3 "_id_seq#, (SELECT MAX(id) FROM " $3 "));"}' | sed "s/#/'/g" | psql <DATABASE> -f -

克劳斯的答案是最有用的，除了有一点遗漏:你 必须在select语句中添加DISTINCT。

但是，如果您确定没有表+列名是等价的 对于两个不同的表，您还可以使用:

select sequence_name, --PG_CLASS.relname, PG_ATTRIBUTE.attname
       reset_sequence(split_part(sequence_name, '_id_seq',1))
from PG_CLASS
join PG_ATTRIBUTE on PG_ATTRIBUTE.attrelid = PG_CLASS.oid
join information_schema.sequences
     on information_schema.sequences.sequence_name = PG_CLASS.relname || '_' || PG_ATTRIBUTE.attname
where sequence_schema='public';

哪个是user457226的扩展方案适用的情况下 一些感兴趣的列名不是“ID”。

重置所有公共序列

CREATE OR REPLACE FUNCTION "reset_sequence" (tablename text) RETURNS "pg_catalog"."void" AS 
$body$  
  DECLARE 
  BEGIN 
  EXECUTE 'SELECT setval( ''' 
  || tablename  
  || '_id_seq'', ' 
  || '(SELECT id + 1 FROM "' 
  || tablename  
  || '" ORDER BY id DESC LIMIT 1), false)';  
  END;  
$body$  LANGUAGE 'plpgsql';

select sequence_name, reset_sequence(split_part(sequence_name, '_id_seq',1)) from information_schema.sequences
        where sequence_schema='public';

重置所有序列，除了每个表的主键是“id”外，对名称没有任何假设:

CREATE OR REPLACE FUNCTION "reset_sequence" (tablename text, columnname text)
RETURNS "pg_catalog"."void" AS
$body$
DECLARE
BEGIN
    EXECUTE 'SELECT setval( pg_get_serial_sequence(''' || tablename || ''', ''' || columnname || '''),
    (SELECT COALESCE(MAX(id)+1,1) FROM ' || tablename || '), false)';
END;
$body$  LANGUAGE 'plpgsql';

select table_name || '_' || column_name || '_seq', reset_sequence(table_name, column_name) from information_schema.columns where column_default like 'nextval%';

所以我可以告诉你，在这个帖子里没有足够的意见或重新发明的轮子，所以我决定让事情变得有趣。

下面是一个程序:

关注(仅影响)与表相关的序列 适用于串行和生成的AS身份列 适用于good_column_names和“BAD_column_123”名称 如果表为空，则自动分配相应序列定义的起始值 允许只影响特定的序列(在schema.table.column表示法中) 有预览模式

CREATE OR REPLACE PROCEDURE pg_reset_all_table_sequences(
    IN commit_mode BOOLEAN DEFAULT FALSE
,   IN mask_in TEXT DEFAULT NULL
) AS
$$
DECLARE
    sql_reset TEXT;
    each_sec RECORD;
    new_val TEXT;
BEGIN

sql_reset :=
$sql$
SELECT setval(pg_get_serial_sequence('%1$s.%2$s', '%3$s'), coalesce(max("%3$s"), %4$s), false) FROM %1$s.%2$s;
$sql$
;

FOR each_sec IN (

    SELECT
        quote_ident(table_schema) as table_schema
    ,   quote_ident(table_name) as table_name
    ,   column_name
    ,   coalesce(identity_start::INT, seqstart) as min_val
    FROM information_schema.columns
    JOIN pg_sequence ON seqrelid = pg_get_serial_sequence(quote_ident(table_schema)||'.'||quote_ident(table_name) , column_name)::regclass
    WHERE
        (is_identity::boolean OR column_default LIKE 'nextval%') -- catches both SERIAL and IDENTITY sequences

    -- mask on column address (schema.table.column) if supplied
    AND coalesce( table_schema||'.'||table_name||'.'||column_name = mask_in, TRUE )
)
LOOP

IF commit_mode THEN
    EXECUTE format(sql_reset, each_sec.table_schema, each_sec.table_name, each_sec.column_name, each_sec.min_val) INTO new_val;
    RAISE INFO 'Resetting sequence for: %.% (%) to %'
        ,   each_sec.table_schema
        ,   each_sec.table_name
        ,   each_sec.column_name
        ,   new_val
    ;
ELSE
    RAISE INFO 'Sequence found for resetting: %.% (%)'
        ,   each_sec.table_schema
        ,   each_sec.table_name
        ,   each_sec.column_name
    ;
END IF
;

END LOOP;

END
$$
LANGUAGE plpgsql
;

预览:

调用pg_reset_all_table_sequences ();

提交:

调用pg_reset_all_table_sequences(真正的);

只指定你的目标表:

调用pg_reset_all_table_sequences(“schema.table.column”);

如果您在加载自定义SQL数据进行初始化时看到此错误，另一种避免此错误的方法是:

而不是写:

INSERT INTO book (id, name, price) VALUES (1 , 'Alchemist' , 10),

从初始数据中删除id(主键)

INSERT INTO book (name, price) VALUES ('Alchemist' , 10),

这使Postgres序列保持同步!