更新模式中用作ID的所有序列的方法:

DO $$ DECLARE
  r RECORD;
BEGIN
FOR r IN (SELECT tablename, pg_get_serial_sequence(tablename, 'id') as sequencename
          FROM pg_catalog.pg_tables
          WHERE schemaname='YOUR_SCHEMA'
          AND tablename IN (SELECT table_name 
                            FROM information_schema.columns 
                            WHERE table_name=tablename and column_name='id')
          order by tablename)
LOOP
EXECUTE
        'SELECT setval(''' || r.sequencename || ''', COALESCE(MAX(id), 1), MAX(id) IS NOT null)
         FROM ' || r.tablename || ';';
END LOOP;
END $$;

这将重置所有来自public的序列，不假设表或列名。在8.4版上测试

CREATE OR REPLACE FUNCTION "reset_sequence" (tablename text, columnname text, sequence_name text) 
    RETURNS "pg_catalog"."void" AS 
    
    $body$  
      DECLARE 
      BEGIN 
    
      EXECUTE 'SELECT setval( ''' || sequence_name  || ''', ' || '(SELECT MAX(' || columnname || 
          ') FROM ' || tablename || ')' || '+1)';
    
      END;  
    
    $body$  LANGUAGE 'plpgsql';
    
    
SELECT table_name || '_' || column_name || '_seq', 
    reset_sequence(table_name, column_name, table_name || '_' || column_name || '_seq') 
FROM information_schema.columns where column_default like 'nextval%';

这个问题发生在我使用实体框架创建数据库，然后用初始数据播种数据库时，这使得序列不匹配。

我通过创建一个脚本来解决这个问题，在播种数据库后运行:

DO
$do$
DECLARE tablename text;
BEGIN
    -- change the where statments to include or exclude whatever tables you need
    FOR tablename IN SELECT table_name FROM information_schema.tables WHERE table_schema='public' AND table_type='BASE TABLE' AND table_name != '__EFMigrationsHistory'
        LOOP
            EXECUTE format('SELECT setval(pg_get_serial_sequence(''"%s"'', ''Id''), (SELECT MAX("Id") + 1 from "%s"))', tablename, tablename);
    END LOOP;
END
$do$

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

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 -

重新启动所有序列到1使用:

-- Create Function
CREATE OR REPLACE FUNCTION "sy_restart_seq_to_1" (
    relname TEXT
)
RETURNS "pg_catalog"."void" AS
$BODY$

DECLARE

BEGIN
    EXECUTE 'ALTER SEQUENCE '||relname||' RESTART WITH 1;';
END;
$BODY$

LANGUAGE 'plpgsql';

-- Use Function
SELECT 
    relname
    ,sy_restart_seq_to_1(relname)
FROM pg_class
WHERE relkind = 'S';

选择setval…使JDBC变得无聊，所以这里有一种与java兼容的方法:

-- work around JDBC 'A result was returned when none was expected.'
-- fix broken nextval due to poorly written 20140320100000_CreateAdminUserRoleTables.sql
DO 'BEGIN PERFORM setval(pg_get_serial_sequence(''admin_user_role_groups'', ''id''), 1 + COALESCE(MAX(id), 0), FALSE) FROM admin_user_role_groups; END;';