Jak zresetować sekwencję klawiszy głównych postgres, gdy nie jest zsynchronizowana?

pg_get_serial_sequence może być stosowany w celu uniknięcia błędnych założeń dotyczących nazwy sekwencji. To resetuje sekwencję w jednym ujęciu:

SELECT pg_catalog.setval(pg_get_serial_sequence('table_name', 'id'), (SELECT MAX(id) FROM table_name)+1);

Lub bardziej zwięźle:

SELECT pg_catalog.setval(pg_get_serial_sequence('table_name', 'id'), MAX(id)) FROM table_name;

Jednak ta forma nie może poprawnie obsłużyć pustych tabel, ponieważ max (id) jest równe null i nie można ustawić wartości 0, ponieważ byłaby poza zakresem sekwencji. Jednym z obejścia tego problemu jest odwołanie się do składni ALTER SEQUENCE, tj.

ALTER SEQUENCE table_name_id_seq RESTART WITH 1;
ALTER SEQUENCE table_name_id_seq RESTART; -- 8.4 or higher

Ale {[7] } ma ograniczone zastosowanie, ponieważ nazwa sekwencji i wartość restart nie może być wyrażeniem.

SELECT setval(pg_get_serial_sequence('t1', 'id'), coalesce(max(id),0) + 1, false) FROM t1;

To zaznacza wszystkie moje pola:

1. unika twardego kodowania rzeczywistej nazwy sekwencji
2. poprawnie obsługuje puste tabele
3. obsługuje tabele z istniejącymi danymi i nie pozostawia dziura w sekwencji

Na koniec zauważ, że pg_get_serial_sequence działa tylko wtedy, gdy sekwencja jest własnością kolumny. Będzie tak, jeśli kolumna inkrementująca została zdefiniowana jako typ serial, jednak jeśli sekwencja została dodana ręcznie, konieczne jest również wykonanie ALTER SEQUENCE .. OWNED BY.

Tzn. jeśli do tworzenia tabeli użyto typu serial, powinno to działać:

CREATE TABLE t1 (
  id serial,
  name varchar(20)
);

SELECT pg_get_serial_sequence('t1', 'id'); -- returns 't1_id_seq'

-- reset the sequence, regardless whether table has rows or not:
SELECT setval(pg_get_serial_sequence('t1', 'id'), coalesce(max(id),0) + 1, false) FROM t1;

Ale jeśli sekwencje zostały dodane ręcznie:

CREATE TABLE t2 (
  id integer NOT NULL,
  name varchar(20)
);

CREATE SEQUENCE t2_custom_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER TABLE t2 ALTER COLUMN id SET DEFAULT nextval('t2_custom_id_seq'::regclass);

ALTER SEQUENCE t2_custom_id_seq OWNED BY t2.id; -- required for pg_get_serial_sequence

SELECT pg_get_serial_sequence('t2', 'id'); -- returns 't2_custom_id_seq'

-- reset the sequence, regardless whether table has rows or not:
SELECT setval(pg_get_serial_sequence('t2', 'id'), coalesce(max(id),0) + 1, false) FROM t1;

The Najkrótsza i najszybsza sposób:

SELECT setval('tbl_tbl_id_seq', max(tbl_id)) FROM tbl;

tbl_id being the serial kolumna tabeli tbl, rysunek z sekwencji tbl_tbl_id_seq (która jest domyślną nazwą automatyczną).

Jeśli nie znasz nazwy załączonej sekwencji (która nie musi być w domyślnej formie), użyj pg_get_serial_sequence():

SELECT setval(pg_get_serial_sequence('tbl', 'tbl_id'), max(tbl_id)) FROM tbl;

Tutaj nie ma żadnego błędu off-by-one. Instrukcja:

Dwuetapowa forma ustawia pole sekwencji last_value do podanej wartości i ustawia swoje pole is_called na true, co oznacza, że next nextval przyspieszy sekwencję przed zwróceniem wartości.

Jeśli tabela może być pusta i , aby w tym przypadku zacząć od 1:

SELECT setval(pg_get_serial_sequence('tbl', 'tbl_id')
            , COALESCE(max(tbl_id) + 1, 1)
            , false)
FROM tbl;

Nie możemy po prostu użyć postaci 2-parametrowej i zacząć od 0, ponieważ dolna granica sekwencji jest 1 domyślnie (chyba dostosowane).

Współbieżność

W celu ochrony przed jednoczesną aktywnością sekwencji lub zapisem do tabeli w powyższych zapytaniach, lock the table w trybie {[13] }. Utrzymuje współbieżne transakcje przed zapisaniem większej liczby (lub czegokolwiek w ogóle).

Aby również wziąć pod uwagę klientów, którzy mogli pobrać numery sekwencji z góry bez żadnych blokad na głównej tabeli, jednak (może się to zdarzyć w niektórych konfiguracjach), tylko zwiększ bieżącą wartość Sekwencja, nigdy jej nie zmniejszaj. Może się to wydawać paranoiczne, ale jest to zgodne z naturą sekwencji i obroną przed kwestiami współbieżności.

BEGIN;

LOCK TABLE tbl IN SHARE MODE;

SELECT setval('tbl_tbl_id_seq', max(tbl_id))
FROM   tbl
HAVING max(tbl_id) > (SELECT last_value FROM tbl_tbl_id_seq); -- prevent lower number

COMMIT;

SHARE tryb jest wystarczająco silny do tego celu. Instrukcja:

Ten tryb chroni tabelę przed jednoczesną zmianą danych.

Koliduje z trybem ROW EXCLUSIVE.

Komendy UPDATE, DELETE, i INSERT nabyć ten tryb blokady w tabeli docelowej

Spowoduje to zresetowanie wszystkich sekwencji z publicznych, bez zakładania nazw tabel lub kolumn. Testowane na wersji 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%';

ALTER SEQUENCE_NAME RESTART WITH (SELECT max (id) FROM table_name); Nie działa.

Skopiowane z @tardate odpowiedź:

SELECT setval(pg_get_serial_sequence('table_name', 'id'), MAX(id)) FROM table_name;

To polecenie tylko do zmiany automatycznie wygenerowanej wartości sekwencji kluczy w postgresql

ALTER SEQUENCE "your_sequence_name" RESTART WITH 0;

W miejsce zera możesz umieścić dowolną liczbę, z której chcesz ponownie uruchomić sekwencję.

Domyślna nazwa sekwencji będzie "TableName_FieldName_seq". Na przykład, jeśli nazwa tabeli to "MyTable", a nazwa pola to "MyID", to nazwa sekwencji będzie "MyTable_MyID_seq".

This is answer is same as @ murugesanponappan ' s answer, but there is a syntax error in his solution. nie można użyć sub query (select max()...) w alter polecenie. Więc albo musisz użyć stałej wartości liczbowej, albo musisz użyć zmiennej zamiast sub query.

Resetuj wszystkie sekwencje, brak założeń co do nazw, z wyjątkiem tego, że kluczem głównym każdej tabeli jest "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%';

Funkcje te są obarczone niebezpieczeństwami, gdy nazwy sekwencji, kolumn, nazw tabel lub nazw schematów mają zabawne znaki, takie jak spacje, znaki interpunkcyjne i tym podobne. Napisałem to:

CREATE OR REPLACE FUNCTION sequence_max_value(oid) RETURNS bigint
VOLATILE STRICT LANGUAGE plpgsql AS  $$
DECLARE
 tabrelid oid;
 colname name;
 r record;
 newmax bigint;
BEGIN
 FOR tabrelid, colname IN SELECT attrelid, attname
               FROM pg_attribute
              WHERE (attrelid, attnum) IN (
                      SELECT adrelid::regclass,adnum
                        FROM pg_attrdef
                       WHERE oid IN (SELECT objid
                                       FROM pg_depend
                                      WHERE refobjid = $1
                                            AND classid = 'pg_attrdef'::regclass
                                    )
          ) LOOP
      FOR r IN EXECUTE 'SELECT max(' || quote_ident(colname) || ') FROM ' || tabrelid::regclass LOOP
          IF newmax IS NULL OR r.max > newmax THEN
              newmax := r.max;
          END IF;
      END LOOP;
  END LOOP;
  RETURN newmax;
END; $$ ;

Możesz wywołać ją dla pojedynczej sekwencji, przekazując jej OID i zwróci najwyższą liczbę używaną przez dowolną tabelę, która ma sekwencję jako domyślną; lub możesz uruchomić ją za pomocą zapytania takiego jak to, aby zresetować wszystkie sekwencje w bazie danych:

 select relname, setval(oid, sequence_max_value(oid))
   from pg_class
  where relkind = 'S';

Używanie innego quala możesz zresetować tylko sekwencję w określonym schemacie i tak dalej. Na przykład, jeśli chcesz dostosować sekwencje w schemacie "public":

select relname, setval(pg_class.oid, sequence_max_value(pg_class.oid))
  from pg_class, pg_namespace
 where pg_class.relnamespace = pg_namespace.oid and
       nspname = 'public' and
       relkind = 'S';

Zauważ, że ze względu na działanie setval (), nie musisz dodawać 1 do wyniku.

Na zakończenie muszę ostrzec, że niektóre bazy danych wydają się mieć domyślne łącza do sekwencji w sposób, który nie pozwala katalogom systemowym uzyskać pełnej informacji o nich. Dzieje się tak, gdy widzisz takie rzeczy w \d psql:

alvherre=# \d baz
                     Tabla «public.baz»
 Columna |  Tipo   |                 Modificadores                  
---------+---------+------------------------------------------------
 a       | integer | default nextval(('foo_a_seq'::text)::regclass)

Zauważ, że wywołanie nextval () w tej klauzuli domyślnej ma ::text cast oprócz ::regclass cast. I think jest to spowodowane tym, że bazy danych są pg_dump ' em ze starych wersji PostgreSQL. Stanie się to, że powyższa funkcja sequence_max_value() zignoruje taką tabelę. Aby rozwiązać ten problem, możesz przedefiniować domyślną klauzulę, aby odnosić się bezpośrednio do sekwencji bez obsady:

alvherre=# alter table baz alter a set default nextval('foo_a_seq');
ALTER TABLE

Następnie psql wyświetla go poprawnie:

alvherre=# \d baz
                     Tabla «public.baz»
 Columna |  Tipo   |             Modificadores              
---------+---------+----------------------------------------
 a       | integer | default nextval('foo_a_seq'::regclass)

Jak tylko to naprawisz, funkcja działa poprawnie zarówno dla tej tabeli, jak i dla wszystkich innych, które mogą używać tej samej sekwencji.

Resetuj całą sekwencję z public

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

Proponuję takie rozwiązanie znalezione na postgres wiki. Aktualizuje wszystkie sekwencje tabel.

SELECT 'SELECT SETVAL(' ||
       quote_literal(quote_ident(PGT.schemaname) || '.' || quote_ident(S.relname)) ||
       ', COALESCE(MAX(' ||quote_ident(C.attname)|| '), 1) ) FROM ' ||
       quote_ident(PGT.schemaname)|| '.'||quote_ident(T.relname)|| ';'
FROM pg_class AS S,
     pg_depend AS D,
     pg_class AS T,
     pg_attribute AS C,
     pg_tables AS PGT
WHERE S.relkind = 'S'
    AND S.oid = D.objid
    AND D.refobjid = T.oid
    AND D.refobjid = C.attrelid
    AND D.refobjsubid = C.attnum
    AND T.relname = PGT.tablename
ORDER BY S.relname;

Jak używać (z Postgres wiki):

• zapisz to do pliku, powiedz ' reset.sql "
• Uruchom plik i zapisz jego wyjście w sposób, który nie zawiera zwykłych Nagłówków, a następnie uruchom to wyjście. Przykład:

Przykład:

psql -Atq -f reset.sql -o temp
psql -f temp
rm temp

Oryginalny artykuł(również z fixem dla własności sekwencji) tutaj

Yet another plpgsql-resetuje się tylko wtedy, gdy max(att) > then lastval

do --check seq not in sync
$$
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;
$$
;

Również komentowanie linii --execute format('alter sequence spowoduje wyświetlenie listy, a nie Resetowanie wartości

Ten problem występuje u mnie, gdy używam encji framework do tworzenia bazy danych, a następnie seed bazy danych z danymi początkowymi, co powoduje niedopasowanie sekwencji.

Rozwiązałem to tworząc skrypt do uruchomienia po zasiedzeniu bazy danych:

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$

Niektóre naprawdę hardcore odpowiedzi tutaj, zakładam, że kiedyś było naprawdę źle w czasie, kiedy to zostało zadane, ponieważ wiele odpowiedzi z tutaj nie działa dla wersji 9.3. Dokumentacja od wersji 8.0 dostarcza odpowiedzi na to pytanie:

SELECT setval('serial', max(id)) FROM distributors;

Również, jeśli musisz dbać o nazwy sekwencji uwzględniające wielkość liter, tak to robisz:

SELECT setval('"Serial"', max(id)) FROM distributors;

Moja wersja używa pierwszej, z pewnym sprawdzaniem błędów...

BEGIN;
CREATE OR REPLACE FUNCTION reset_sequence(_table_schema text, _tablename text, _columnname text, _sequence_name text)
RETURNS pg_catalog.void AS
$BODY$
DECLARE
BEGIN
 PERFORM 1
 FROM information_schema.sequences
 WHERE
  sequence_schema = _table_schema AND
  sequence_name = _sequence_name;
 IF FOUND THEN
  EXECUTE 'SELECT setval( ''' || _table_schema || '.' || _sequence_name  || ''', ' || '(SELECT MAX(' || _columnname || ') FROM ' || _table_schema || '.' || _tablename || ')' || '+1)';
 ELSE
  RAISE WARNING 'SEQUENCE NOT UPDATED ON %.%', _tablename, _columnname;
 END IF;
END; 
$BODY$
 LANGUAGE 'plpgsql';

SELECT reset_sequence(table_schema, table_name, column_name, table_name || '_' || column_name || '_seq')
FROM information_schema.columns
WHERE column_default LIKE 'nextval%';

DROP FUNCTION reset_sequence(_table_schema text, _tablename text, _columnname text, _sequence_name text) ;
COMMIT;

Składanie wszystkiego razem

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

Naprawi ' id' sekwencję podanej tabeli (jak zwykle jest to konieczne na przykład w django).

Sprawdź ponownie wszystkie sekwencje w publicznej funkcji schematu

CREATE OR REPLACE FUNCTION public.recheck_sequence (
)
RETURNS void AS
$body$
DECLARE
  _table_name VARCHAR;
  _column_name VARCHAR;  
  _sequence_name VARCHAR;
BEGIN
  FOR _table_name IN SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname = 'public' LOOP
    FOR _column_name IN SELECT column_name FROM information_schema.columns WHERE table_name = _table_name LOOP
        SELECT pg_get_serial_sequence(_table_name, _column_name) INTO _sequence_name;
        IF _sequence_name IS NOT NULL THEN 
            EXECUTE 'SELECT setval('''||_sequence_name||''', COALESCE((SELECT MAX('||quote_ident(_column_name)||')+1 FROM '||quote_ident(_table_name)||'), 1), FALSE);';
        END IF;
    END LOOP;   
  END LOOP;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

Zanim jeszcze nie próbowałem kodu : w poniższym wpisie wersja kodu sql dla rozwiązań Klaus i user457226 który działał na moim pc [Postgres 8.3], z niewielkimi poprawkami dla Klausa i mojej wersji dla user457226.

Rozwiązanie Klausa:

drop function IF EXISTS rebuilt_sequences() RESTRICT;
CREATE OR REPLACE FUNCTION  rebuilt_sequences() RETURNS integer as
$body$
  DECLARE sequencedefs RECORD; c integer ;
  BEGIN
    FOR sequencedefs IN Select
      constraint_column_usage.table_name as tablename,
      constraint_column_usage.table_name as tablename, 
      constraint_column_usage.column_name as columnname,
      replace(replace(columns.column_default,'''::regclass)',''),'nextval(''','') as sequencename
      from information_schema.constraint_column_usage, information_schema.columns
      where constraint_column_usage.table_schema ='public' AND 
      columns.table_schema = 'public' AND columns.table_name=constraint_column_usage.table_name
      AND constraint_column_usage.column_name = columns.column_name
      AND columns.column_default is not null
   LOOP    
      EXECUTE 'select max('||sequencedefs.columnname||') from ' || sequencedefs.tablename INTO c;
      IF c is null THEN c = 0; END IF;
      IF c is not null THEN c = c+ 1; END IF;
      EXECUTE 'alter sequence ' || sequencedefs.sequencename ||' restart  with ' || c;
   END LOOP;

   RETURN 1; END;
$body$ LANGUAGE plpgsql;

select rebuilt_sequences();

User457226 rozwiązanie:

--drop function IF EXISTS reset_sequence (text,text) RESTRICT;
CREATE OR REPLACE FUNCTION "reset_sequence" (tablename text,columnname text) RETURNS bigint --"pg_catalog"."void"
AS
$body$
  DECLARE seqname character varying;
          c integer;
  BEGIN
    select tablename || '_' || columnname || '_seq' into seqname;
    EXECUTE 'SELECT max("' || columnname || '") FROM "' || tablename || '"' into c;
    if c is null then c = 0; end if;
    c = c+1; --because of substitution of setval with "alter sequence"
    --EXECUTE 'SELECT setval( "' || seqname || '", ' || cast(c as character varying) || ', false)'; DOES NOT WORK!!!
    EXECUTE 'alter sequence ' || seqname ||' restart with ' || cast(c as character varying);
    RETURN nextval(seqname)-1;
  END;
$body$ LANGUAGE 'plpgsql';

select sequence_name, PG_CLASS.relname, PG_ATTRIBUTE.attname,
       reset_sequence(PG_CLASS.relname,PG_ATTRIBUTE.attname)
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 || '_seq'
where sequence_schema='public';

Ta odpowiedź jest kopią od mauro.

drop function IF EXISTS rebuilt_sequences() RESTRICT;
CREATE OR REPLACE FUNCTION  rebuilt_sequences() RETURNS integer as
$body$
  DECLARE sequencedefs RECORD; c integer ;
  BEGIN
    FOR sequencedefs IN Select
      DISTINCT(constraint_column_usage.table_name) as tablename,
      constraint_column_usage.column_name as columnname,
      replace(replace(columns.column_default,'''::regclass)',''),'nextval(''','') as sequencename
      from information_schema.constraint_column_usage, information_schema.columns
      where constraint_column_usage.table_schema ='public' AND 
      columns.table_schema = 'public' AND columns.table_name=constraint_column_usage.table_name
      AND constraint_column_usage.column_name = columns.column_name
      AND columns.column_default is not null 
      ORDER BY sequencename
   LOOP    
      EXECUTE 'select max('||sequencedefs.columnname||') from ' || sequencedefs.tablename INTO c;
      IF c is null THEN c = 0; END IF;
      IF c is not null THEN c = c+ 1; END IF;
      EXECUTE 'alter sequence ' || sequencedefs.sequencename ||' minvalue '||c ||' start ' || c ||' restart  with ' || c;
   END LOOP;

   RETURN 1; END;
$body$ LANGUAGE plpgsql;

select rebuilt_sequences();

Aby ponownie uruchomić całą sekwencję do 1 Użyj:

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

ODPOWIEDŹ Klausa jest najbardziej przydatna, wykonywana dla Małej miss: you trzeba dodać DISTINCT w instrukcji select.

Jeśli jednak masz pewność, że żadna nazwa table+column nie może być równoważna dla dwóch różnych tabel, można również użyć:

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

Które jest rozszerzeniem rozwiązania user457226 w przypadku, gdy nazwa kolumny nie jest 'ID'.

Jeśli widzisz ten błąd podczas ładowania niestandardowych danych SQL do inicjalizacji, innym sposobem uniknięcia tego jest:

Zamiast pisać:

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

Usuń id (Klucz podstawowy) z danych początkowych

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

Dzięki temu Sekwencja Postgres jest zsynchronizowana !

Spędziłem godzinę próbując uzyskać odpowiedź djsnowsill do pracy z bazą danych przy użyciu mieszanych tabel przypadków i kolumn, a następnie w końcu natknąłem się na rozwiązanie dzięki komentarzowi Manuela Darveau, ale pomyślałem, że mogę to nieco jaśniejsze dla wszystkich: {]}

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

SELECT format('%s_%s_seq',table_name,column_name), reset_sequence(table_name,column_name) 
FROM information_schema.columns WHERE column_default like 'nextval%';

Ma to na celu:

• nie zakładając, że kolumna ID jest pisana w określony sposób.
• nie zakładając, że wszystkie tabele mają sekwencję.
• praca dla mieszanych nazw tabel/kolumn.
• za pomocą format bardziej zwięzły.

Aby wyjaśnić, problem polegał na tym, że pg_get_serial_sequence wymaga ciągów, aby ustalić, do czego się odnosisz, więc jeśli to zrobisz: {]}

"TableName" --it thinks it's a table or column
'TableName' --it thinks it's a string, but makes it lower case
'"TableName"' --it works!

Osiąga się to za pomocą ''%1$I'' w łańcuchu formatu, '' tworzy apostrof 1$ oznacza pierwsze arg, a I oznacza w cudzysłowach

select 'SELECT SETVAL(' || seq [ 1] || ', COALESCE(MAX('||column_name||')+1, 1) ) FROM '||table_name||';'
from (
       SELECT table_name, column_name, column_default, regexp_match(column_default, '''.*''') as seq
       from information_schema.columns
       where column_default ilike 'nextval%'
     ) as sequense_query

Try reindex .

UPDATE: jak zaznaczono w komentarzach, była to odpowiedź na pierwotne pytanie.

Brzydki hack, aby naprawić go za pomocą magii powłoki, nie jest to świetne rozwiązanie, ale może zainspirować innych z podobnymi problemami:)

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 -

Po prostu uruchom poniższe polecenie:

SELECT setval('my_table_seq', (SELECT max(id) FROM my_table));

Metoda aktualizacji wszystkich sekwencji w schemacie, które są używane jako 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 $$;

Jest tu wiele dobrych odpowiedzi. Miałem taką samą potrzebę po przeładowaniu bazy danych Django.

Ale potrzebowałem:

• All in one Function
• można naprawić jeden lub więcej schematów na raz
• może naprawić wszystkie lub tylko jedną tabelę na raz
• również chciał miły sposób, aby zobaczyć dokładnie, co się zmieniło, lub nie zmieniło

Wydaje się to bardzo podobne do tego, o co pierwotnie pytano.
Dzięki Baldiry i Mauro dostałem po prawej track.

drop function IF EXISTS reset_sequences(text[], text) RESTRICT;
CREATE OR REPLACE FUNCTION reset_sequences(
    in_schema_name_list text[] = '{"django", "dbaas", "metrics", "monitor", "runner", "db_counts"}',
    in_table_name text = '%') RETURNS text[] as
$body$
  DECLARE changed_seqs text[];
  DECLARE sequence_defs RECORD; c integer ;
  BEGIN
    FOR sequence_defs IN
        select
          DISTINCT(ccu.table_name) as table_name,
          ccu.column_name as column_name,
          replace(replace(c.column_default,'''::regclass)',''),'nextval(''','') as sequence_name
          from information_schema.constraint_column_usage ccu,
               information_schema.columns c
          where ccu.table_schema = ANY(in_schema_name_list)
            and ccu.table_schema = c.table_schema
            AND c.table_name = ccu.table_name
            and c.table_name like in_table_name
            AND ccu.column_name = c.column_name
            AND c.column_default is not null
          ORDER BY sequence_name
   LOOP
      EXECUTE 'select max(' || sequence_defs.column_name || ') from ' || sequence_defs.table_name INTO c;
      IF c is null THEN c = 1; else c = c + 1; END IF;
      EXECUTE 'alter sequence ' || sequence_defs.sequence_name || ' restart  with ' || c;
      changed_seqs = array_append(changed_seqs, 'alter sequence ' || sequence_defs.sequence_name || ' restart with ' || c);
   END LOOP;
   changed_seqs = array_append(changed_seqs, 'Done');

   RETURN changed_seqs;
END
$body$ LANGUAGE plpgsql;

Następnie wykonać i zobaczyć zmiany uruchomić:

select *
from unnest(reset_sequences('{"django", "dbaas", "metrics", "monitor", "runner", "db_counts"}'));

Zwraca

activity_id_seq                          restart at 22
api_connection_info_id_seq               restart at 4
api_user_id_seq                          restart at 1
application_contact_id_seq               restart at 20

Więc mogę powiedzieć, że w tym wątku nie ma wystarczającej ilości opinii lub kół na nowo, więc postanowiłem urozmaicić sprawy.

Poniżej znajduje się procedura, która:

• skupia się (dotyczy tylko) na sekwencjach powiązanych z tabelami
• działa zarówno dla szeregowych, jak i generowanych jako kolumny tożsamości
• działa na nazwy good_column_names i "BAD_column_123"
• automatycznie przypisuje określoną wartość początkową sekwencji, jeśli tabela jest pusta
• pozwala tylko dla określonych sekwencji (w schemacie.stolik.zapis kolumnowy)
• posiada tryb podglądu

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
;

Do podglądu:

call pg_reset_all_table_sequences();

Do commit:

call pg_reset_all_table_sequences(true);

Aby określić tylko tabelę docelową:

call pg_reset_all_table_sequences('schema.table.column');

SELECT setval... sprawia, że JDBC bork, więc oto sposób na to zgodny z Javą:

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