Jak zresetować sekwencję klucza głównego postgres, gdy nie synchronizuje się?

Wystąpił problem polegający na tym, że moja sekwencja kluczy podstawowych nie jest zsynchronizowana z wierszami tabeli.

Oznacza to, że po wstawieniu nowego wiersza pojawia się duplikat błędu klucza, ponieważ sekwencja sugerowana w typie danych szeregowych zwraca liczbę, która już istnieje.

Wydaje się, że jest to spowodowane importem / przywróceniem niewłaściwego utrzymywania sekwencji.

-- Login to psql and run the following

-- What is the result?
SELECT MAX(id) FROM your_table;

-- Then run...
-- This should be higher than the last result.
SELECT nextval('your_table_id_seq');

-- If it's not higher... run this set the sequence last to your highest id. 
-- (wise to run a quick pg_dump first...)

BEGIN;
-- protect against concurrent inserts while you update the counter
LOCK TABLE your_table IN EXCLUSIVE MODE;
-- Update the sequence
SELECT setval('your_table_id_seq', COALESCE((SELECT MAX(id)+1 FROM your_table), 1), false);
COMMIT;

Źródło - Ruby Forum

pg_get_serial_sequencemożna użyć, aby uniknąć niepoprawnych założeń dotyczących nazwy sekwencji. Spowoduje to zresetowanie sekwencji 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ługiwać pustych tabel, ponieważ max (id) ma wartość null i nie można ustawić wartości 0, ponieważ byłoby to poza zakresem sekwencji. Jednym z obejść tego problemu jest skorzystanie ze ALTER SEQUENCEskładni, tj

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

Ma jednak ALTER SEQUENCEograniczone zastosowanie, ponieważ nazwa sekwencji i wartość restartu nie mogą być wyrażeniami.

Wydaje się, że najlepszym rozwiązaniem uniwersalnym jest wywoływanie setvalz fałszem jako trzecim parametrem, co pozwala nam określić „następną wartość do użycia”:

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

To zaznacza wszystkie moje pola:

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

Na koniec zauważ, że pg_get_serial_sequencedziała tylko wtedy, gdy sekwencja jest własnością kolumny. Będzie tak w przypadku, gdy kolumna inkrementująca została zdefiniowana jako serialtyp, jednak jeśli sekwencja została dodana ręcznie, należy upewnić ALTER SEQUENCE .. OWNED BYsię, że również zostanie wykonana.

tzn. jeśli serialdo utworzenia tabeli użyto typu, wszystko powinno 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;

Najkrótszy i najszybszy sposób:

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

tbl_idbędąc serialkolumną tabeli tbl, rysując z sekwencji tbl_tbl_id_seq(która jest domyślną nazwą automatyczną).

Jeśli nie znasz nazwy dołą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;

Nie ma tutaj błędu off-by-one. Według dokumentacji:

Formularz dwuparametrowy ustawia pole sekwencji last_valuena określoną wartość i ustawia jej is_calledpole na true, co oznacza, że następny nextvalprzesunie sekwencję przed zwróceniem wartości.

Odważny nacisk moje.

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

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-paremater i zacząć od, 0ponieważ dolna granica sekwencji wynosi domyślnie 1 (chyba że niestandardowe).

Konkurencja

Nie ma jeszcze obrony przed równoczesną aktywnością sekwencji lub zapisuje do tabeli w powyższych zapytaniach. Jeśli to istotne, możesz zablokować tabelę w trybie wyłączności. Uniemożliwia jednoczesnym transakcjom zapisywanie większej liczby podczas próby synchronizacji. (Tymczasowo blokuje również nieszkodliwe zapisy, nie mieszając się z maksymalną liczbą).

Ale nie bierze pod uwagę klientów, którzy mogli wcześniej pobrać numery sekwencyjne bez żadnych blokad głównego stołu (co może się zdarzyć). Aby na to pozwolić, zwiększaj tylko bieżącą wartość sekwencji, nigdy jej nie zmniejszaj. Może się to wydawać paranoikiem, ale jest to zgodne z naturą sekwencji i obrony przed problemami współbieżności.

BEGIN;

LOCK TABLE tbl IN EXCLUSIVE MODE;

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

COMMIT;

Spowoduje to zresetowanie wszystkich sekwencji z publicznego, nie przyjmując żadnych założeń dotyczących nazw tabel lub kolumn. Testowany 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 nazwa_sekwencji RESTART Z (WYBIERZ max (id) FROM nazwa_tabeli); Nie działa

Skopiowano z opóźnionej odpowiedzi:

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

To polecenie służy tylko do zmiany automatycznie generowanej wartości sekwencji kluczy w postgresql

ALTER SEQUENCE "your_sequence_name" RESTART WITH 0;

Zamiast zera możesz podać dowolną liczbę, od której chcesz zrestartować sekwencję.

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

To odpowiedź jest taka sama jak odpowiedź @ murugesanponappan, ale w jego rozwiązaniu występuje błąd składniowy. nie można użyć zapytania podrzędnego (select max()...)w alterpoleceniu. Tak więc albo musisz użyć stałej wartości liczbowej, albo musisz użyć zmiennej zamiast zapytania podrzędnego.

Zresetuj wszystkie sekwencje, bez założeń dotyczących nazw, z wyjątkiem tego, że kluczem podstawowym 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ństwem, gdy nazwy sekwencji, nazwy kolumn, nazwy tabel lub nazwy 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 identyfikator OID, a ona zwróci najwyższy numer używany przez dowolną tabelę, która ma domyślną sekwencję; lub możesz uruchomić go z takim zapytaniem, aby zresetować wszystkie sekwencje w bazie danych:

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

Używając innej jakości możesz zresetować tylko sekwencję w określonym schemacie i tak dalej. Na przykład, jeśli chcesz dostosować sekwencje w schemacie „publicznym”:

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 z powodu działania setval () nie musisz dodawać 1 do wyniku.

Na zakończenie muszę ostrzec, że niektóre bazy danych wydają się mieć domyślne ustawienia łączące z sekwencjami w sposób, który nie pozwala katalogom systemowym mieć pełnych informacji na ich temat. Dzieje się tak, gdy widzisz takie rzeczy w psql \ d:

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 domyślnej klauzuli ma rzutowanie :: text oprócz rzutowania :: regclass. Myślę, że jest to spowodowane tym, że bazy danych są pg_dump'ed ze starych wersji PostgreSQL. To, co się stanie, to funkcja powyższej tabeli sekwencja_max_value () zignoruje taką tabelę. Aby rozwiązać problem, możesz ponownie zdefiniować klauzulę DOMYŚLNĄ, aby odwoływała się do sekwencji bezpośrednio bez rzutowania:

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 dla tej tabeli, jak również dla wszystkich innych, które mogą używać tej samej sekwencji.

Jeszcze inny plpgsql - resetuje tylko jeśli 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;
$$
;

komentowanie wiersza --execute format('alter sequencespowoduje wyświetlenie listy, a nie zresetowanie wartości

Zresetuj całą sekwencję z publicznego

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

Sugeruję to rozwiązanie znalezione na wiki Postgres. Aktualizuje wszystkie sekwencje twoich 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 korzystać (z wiki postgres):

• Zapisz to w pliku, powiedz „reset.sql”
• Uruchom plik i zapisz dane wyjściowe w sposób, który nie obejmuje zwykłych nagłówków, a następnie uruchom dane wyjściowe. Przykład:

Przykład:

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

Artykuł oryginalny (również z poprawką dotyczącą własności sekwencji) tutaj

Oto niektóre naprawdę hardcorowe odpowiedzi, zakładam, że kiedyś było bardzo źle, kiedy o to pytano, ponieważ wiele odpowiedzi tutaj nie działa w wersji 9.3. Dokumentacja od wersji 8.0 daje odpowiedź do tego samego pytania:

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

Ponadto, jeśli musisz zająć się rozróżnianiem wielkości liter w nazwach sekwencji, możesz to zrobić w następujący sposób:

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

Ten problem występuje ze mną, gdy używam struktury encji do utworzenia bazy danych, a następnie zapełniam bazę danych danymi początkowymi, co powoduje niedopasowanie sekwencji.

Rozwiązałem go, tworząc skrypt do uruchomienia po zainicjowaniu 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$

Moja wersja używa pierwszej, z pewną kontrolą 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;

Kładąc wszystko 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 to zwykle konieczne na przykład w przypadku django).

zanim jeszcze nie wypróbowałem kodu: poniżej zamieszczam wersję kodu SQL dla rozwiązań Klausa i user457226, które działały na moim komputerze [Postgres 8.3], z niewielkimi modyfikacjami dla Klausa i mojej wersji dla użytkownika 457226 jeden.

Rozwiązanie Klaus:

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();

rozwiązanie user457226:

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

Sprawdź ponownie całą sekwencję w funkcji schematu publicznego

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;

Aby ponownie uruchomić wszystkie sekwencje 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 użyteczna, wykonywana za krótką chwilę: musisz dodać DISTINCT w instrukcji select.

Jeśli jednak masz pewność, że żadna nazwa tabeli + kolumny nie może być równoważna dla dwóch różnych tabel, możesz 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óry jest rozszerzeniem rozwiązania user457226 dla przypadku, gdy nazwa zainteresowanej kolumny nie jest „ID”.

Jeśli zobaczysz ten błąd podczas ładowania niestandardowych danych SQL do inicjalizacji, możesz tego uniknąć inaczej:

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),

To utrzymuje synchronizację sekwencji Postgres!

Ta odpowiedź jest kopią z 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();

Spędziłem godzinę próbując uzyskać odpowiedź djsnowsill do pracy z bazą danych przy użyciu tabel i kolumn Mixed Case, a potem w końcu natknąłem się na rozwiązanie dzięki komentarzowi Manuela Darveau, ale pomyślałem, że mógłbym to zrobić dla wszystkich nieco jaśniej:

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 tę zaletę, że:

• nie zakładając, że kolumna identyfikatora jest zapisana w określony sposób.
• nie zakładając, że wszystkie tabele mają sekwencję.
• pracuje dla nazw tabel / kolumn o różnej wielkości liter.
• używając formatu, aby być bardziej zwięzłym.

Aby wyjaśnić, problem polegał na tym, że pg_get_serial_sequenceciągi znaków opracowują to, o czym mówisz, 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!

Uzyskuje się to za pomocą ''%1$I''ciągu formatu, ''apostrof 1$oznacza pierwszy argument i Ioznacza w cudzysłowie

Brzydki hack, aby naprawić to 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 -

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

Spróbuj ponownie .

AKTUALIZACJA: Jak wskazano w komentarzach, była to odpowiedź na pierwotne pytanie.

SELECT setval... sprawia, że ​​JDBC jest bork, więc oto sposób na zrobienie tego:

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

Metoda aktualizacji wszystkich sekwencji w schemacie używanych jako identyfikator:

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

Po prostu uruchom poniżej polecenia:

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

Tutaj jest wiele dobrych odpowiedzi. Miałem tę samą potrzebę po przeładowaniu bazy danych Django.

Ale potrzebowałem:

• Wszystko w jednej funkcji
• Można naprawić jeden lub więcej schematów jednocześnie
• Można naprawić wszystkie lub tylko jeden stół na raz
• Chciałem także ciekawy sposób, aby zobaczyć dokładnie, co się zmieniło lub nie zmieniło

Wydaje się, że jest to bardzo podobna potrzeba do pierwotnej prośby.
Dzięki Baldiry i Mauro wprowadziłem mnie na właściwy tor.

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, aby wykonać i zobaczyć zmiany uruchom:

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

Zwroty

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