Jak zresetować sekwencję w postgres i wypełnić kolumnę id nowymi danymi?


126

Mam tabelę z ponad milionem wierszy. Muszę zresetować sekwencję i ponownie przypisać kolumnę id z nowymi wartościami (1, 2, 3, 4 ... itd.). Czy jest to łatwy sposób?


6
Prawdziwe pytanie: dlaczego, u licha, chcesz to zrobić? Przypuszczalnie identyfikator jest kluczem podstawowym, więc zmiana klucza podstawowego nie przynosi żadnych korzyści. Klucz podstawowy to bezsensowna (w twoim przypadku sztuczna) wartość. „Zmiana numeracji” nie służy żadnemu rozsądnemu celowi w relacyjnej bazie danych.
a_horse_with_no_name

2
Początkowo miałem aplikację działającą lokalnie, a następnie skopiowałem dane do produkcji. Ale idczy tam nie zaczynał się od 1. Więc kolejność wyglądała następująco: 150, 151 ..., 300, 1, 2 ... I przypuszczam, że w końcu spowodowałoby to zduplikowane błędy id, gdybym nie zmienił numeracji identyfikatory. Ponadto sortowanie według idjest na ogół lepsze niż sortowanie według created_at. Oto, co zadziałało dla mnie .
x-yuri

Chodzi o to, abyś mógł nadal używać zwykłego int zamiast bigint dla klucza podstawowego w bazie danych, który kontynuuje zwiększanie klucza sekwencyjnego, ale stale otrzymuje nowe dane. Szybko napotkasz limit liczb całkowitych ze znakiem, a jeśli zachowanie istniejącego identyfikatora nie jest ważne, proces ten przywróci Ci możliwe do zarządzania numery identyfikacyjne.
Ben Wilson

Innym zastosowaniem tego jest testowanie. Chcesz zresetować tabelę do znanego stanu przed rozpoczęciem każdego testu i wymaga zresetowania identyfikatorów.
Safa Alai

Odpowiedzi:


204

Jeśli nie chcesz zachować kolejności identyfikatorów, możesz

ALTER SEQUENCE seq RESTART WITH 1;
UPDATE t SET idcolumn=nextval('seq');

Wątpię, aby można było to łatwo zrobić w wybranej przez siebie kolejności bez odtwarzania całego stołu.


4
Nie powinno tak być ALTER SEQUENCE seq RESTART WITH 1;?
Lars Haugseth

5
Może to spowodować zduplikowanie identyfikatorów. Aby temu zapobiec, możesz najpierw ustawić wszystko na bardzo wysokie wartości: UPDATE t SET idcolumn = 1000000 + nextval ('seq'); następnie uruchom powyższy skrypt.
tahagh

5
SELECT setval('seq', 1, FALSE)powinien zrobić to samo (tutaj trzeci argument, FALSE, robi magię, ponieważ pokazuje, że nextvalmusi to być 1 zamiast 2)
Vasilen Donchev

@VassilenDontchev, absolutnie.
Michael Krelin - haker

55

Z PostgreSQL 8.4 lub nowszym nie ma już potrzeby określania WITH 1. Użyta zostanie wartość początkowa, która została zapisana przez CREATE SEQUENCElub ostatnio ustawiona przez ALTER SEQUENCE START WITH(najprawdopodobniej będzie to 1).

Zresetuj sekwencję:

ALTER SEQUENCE seq RESTART;

Następnie zaktualizuj kolumnę ID tabeli:

UPDATE foo SET id = DEFAULT;

Źródło: Dokumenty PostgreSQL


3
Wydaje się, że jest to najlepsza odpowiedź, ponieważ pozwala uniknąć robienia założeń co do wartości początkowej sekwencji.
owczarek

Najlepsza odpowiedź również w moim przypadku. Łączę tę odpowiedź z , która wyjaśnia polecenie ALTER SEQUENCE ... więc zmieniłem `` seq '' na mytable_id_seq, gdzie `` mytable '' to nazwa mojej tabeli, a `` id '' to nazwa mojej kolumny szeregowej
Javi

42

Zresetuj sekwencję:

SELECT setval('sequence_name', 0);

Aktualizacja aktualnych rekordów:

UPDATE foo SET id = DEFAULT;

3
Sekwencja może mieć minimalną wartość większą niż 0. (I domyślna minimalna wartość używana przez typ seriali CREATE SEQUENCEwynosi 1!)
brk

18

Oba podane rozwiązania nie działały u mnie;

> SELECT setval('seq', 0);
ERROR:  setval: value 0 is out of bounds for sequence "seq" (1..9223372036854775807)

setval('seq', 1)rozpoczyna numerację od 2, a także ALTER SEQUENCE seq START 1rozpoczyna numerację od 2, ponieważ seq.is_called ma wartość true (Postgres wersja 9.0.4)

Rozwiązanie, które zadziałało dla mnie to:

> ALTER SEQUENCE seq RESTART WITH 1;
> UPDATE foo SET id = DEFAULT;

1
Mam ten sam problem. Twoje rozwiązanie działa również z PostgreSQL 8.3.10.
PeqNP

17

Tylko dla uproszczenia i wyjaśnienia prawidłowego użycia ALTER SEQUENCE i SELECT setval do resetowania sekwencji:

ALTER SEQUENCE sequence_name RESTART WITH 1;

jest równa

SELECT setval('sequence_name', 1, FALSE);

Każda z instrukcji może zostać użyta do zresetowania sekwencji, a następną wartość można uzyskać za pomocą funkcji nextval („nazwa_sekwencji”), jak również podano tutaj :

nextval('sequence_name')

Dzięki Ali. Właśnie zauważyłem, że kluczowe jest ustawienie tego trzeciego parametru na false za pomocą funkcji
setval

14

Najlepszym sposobem na zresetowanie sekwencji, aby rozpocząć od numeru 1, jest wykonanie następujących czynności:

ALTER SEQUENCE <tablename>_<id>_seq RESTART WITH 1

Na przykład dla tabeli użytkowników byłoby to:

ALTER SEQUENCE users_id_seq RESTART WITH 1

6

Aby zachować kolejność rzędów:

UPDATE thetable SET rowid=col_serial FROM 
(SELECT rowid, row_number() OVER ( ORDER BY lngid) AS col_serial FROM thetable ORDER BY lngid) AS t1 
WHERE thetable.rowid=t1.rowid;

4

FYI: Jeśli chcesz określić nową wartość początkową z zakresu identyfikatorów (na przykład 256 - 10000000):

SELECT setval('"Sequence_Name"', 
       (SELECT coalesce(MAX("ID"),255) 
           FROM "Table_Name" 
           WHERE "ID" < 10000000 and "ID" >= 256)+1
       ); 

2

Samo zresetowanie sekwencji i zaktualizowanie wszystkich wierszy może spowodować powielenie błędów identyfikatora. W wielu przypadkach musisz dwukrotnie zaktualizować wszystkie wiersze. Najpierw z wyższymi identyfikatorami, aby uniknąć duplikatów, a następnie z identyfikatorami, które faktycznie chcesz.

Unikaj dodawania stałej kwoty do wszystkich identyfikatorów (zgodnie z zaleceniami w innych komentarzach). Co się stanie, jeśli masz więcej wierszy niż ta stała kwota? Zakładając, że następna wartość sekwencji jest wyższa niż wszystkie identyfikatory istniejących wierszy (chcesz tylko wypełnić luki), zrobiłbym to tak:

UPDATE table SET id = DEFAULT;
ALTER SEQUENCE seq RESTART;
UPDATE table SET id = DEFAULT;

1

W moim przypadku osiągnąłem to dzięki:

ALTER SEQUENCE table_tabl_id_seq RESTART WITH 6;

Gdzie moja tabela nazywa się table


Dziękujemy za dołączenie konkretnego przykładu z nazwą tabeli. Pozostałe odpowiedzi były nieco zbyt niejednoznaczne.
Brylie Christopher Oxley

0

Zainspirowany innymi odpowiedziami tutaj, stworzyłem funkcję SQL do wykonywania migracji sekwencji. Funkcja przenosi sekwencję kluczy podstawowych do nowej, ciągłej sekwencji, zaczynając od dowolnej wartości (> = 1) w obrębie lub poza istniejącym zakresem sekwencji.

Wyjaśniam tutaj, jak wykorzystałem tę funkcję do migracji dwóch baz danych z tym samym schematem, ale różnymi wartościami do jednej bazy danych.

Najpierw funkcja (która wyświetla wygenerowane polecenia SQL, aby było jasne, co się właściwie dzieje):

CREATE OR REPLACE FUNCTION migrate_pkey_sequence
  ( arg_table      text
  , arg_column     text
  , arg_sequence   text
  , arg_next_value bigint  -- Must be >= 1
  )
RETURNS int AS $$
DECLARE
  result int;
  curr_value bigint = arg_next_value - 1;
  update_column1 text := format
    ( 'UPDATE %I SET %I = nextval(%L) + %s'
    , arg_table
    , arg_column
    , arg_sequence
    , curr_value
    );
  alter_sequence text := format
    ( 'ALTER SEQUENCE %I RESTART WITH %s'
    , arg_sequence
    , arg_next_value
    );
  update_column2 text := format
    ( 'UPDATE %I SET %I = DEFAULT'
    , arg_table
    , arg_column
    );
  select_max_column text := format
    ( 'SELECT coalesce(max(%I), %s) + 1 AS nextval FROM %I'
    , arg_column
    , curr_value
    , arg_table
    );
BEGIN
  -- Print the SQL command before executing it.
  RAISE INFO '%', update_column1;
  EXECUTE update_column1;
  RAISE INFO '%', alter_sequence;
  EXECUTE alter_sequence;
  RAISE INFO '%', update_column2;
  EXECUTE update_column2;
  EXECUTE select_max_column INTO result;
  RETURN result;
END $$ LANGUAGE plpgsql;

Funkcja migrate_pkey_sequenceprzyjmuje następujące argumenty:

  1. arg_table: nazwa tabeli (np. 'example')
  2. arg_column: nazwa kolumny klucza podstawowego (np. 'id')
  3. arg_sequence: nazwa sekwencji (np. 'example_id_seq')
  4. arg_next_value: następna wartość dla kolumny po migracji

Wykonuje następujące operacje:

  1. Przenieś wartości klucza podstawowego do wolnego zakresu. Zakładam, że nextval('example_id_seq')następuje to max(id)i że sekwencja zaczyna się od 1. Dotyczy to również przypadku, w którym arg_next_value > max(id).
  2. Przenieś wartości klucza podstawowego do ciągłego zakresu, zaczynając od arg_next_value. Kolejność kluczowych wartości jest zachowana, ale dziury w zakresie nie są zachowywane.
  3. Wydrukuj następną wartość, która pojawi się w sekwencji. Jest to przydatne, jeśli chcesz przenieść kolumny z innej tabeli i scalić je z tą.

Aby zademonstrować, używamy sekwencji i tabeli zdefiniowanych w następujący sposób (np. Używając psql):

# CREATE SEQUENCE example_id_seq
  START WITH 1
  INCREMENT BY 1
  NO MINVALUE
  NO MAXVALUE
  CACHE 1;
# CREATE TABLE example
  ( id bigint NOT NULL DEFAULT nextval('example_id_seq'::regclass)
  );

Następnie wstawiamy kilka wartości (zaczynając na przykład od 3):

# ALTER SEQUENCE example_id_seq RESTART WITH 3;
# INSERT INTO example VALUES (DEFAULT), (DEFAULT), (DEFAULT);
-- id: 3, 4, 5

Na koniec migrujemy example.idwartości, aby rozpocząć od 1.

# SELECT migrate_pkey_sequence('example', 'id', 'example_id_seq', 1);
INFO:  00000: UPDATE example SET id = nextval('example_id_seq') + 0
INFO:  00000: ALTER SEQUENCE example_id_seq RESTART WITH 1
INFO:  00000: UPDATE example SET id = DEFAULT
 migrate_pkey_sequence
-----------------------
                     4
(1 row)

Wynik:

# SELECT * FROM example;
 id
----
  1
  2
  3
(3 rows)

0

Nawet kolumna autoinkrementacji nie jest PK (w tym przykładzie nazywa się seq - inaczej sekwencja), można to osiągnąć za pomocą wyzwalacza:

DROP TABLE, JEŚLI ISTNIEJE devops_guide KASKADA;

SELECT 'create the "devops_guide" table'
;
   CREATE TABLE devops_guide (
      guid           UUID NOT NULL DEFAULT gen_random_uuid()
    , level          integer NULL
    , seq            integer NOT NULL DEFAULT 1
    , name           varchar (200) NOT NULL DEFAULT 'name ...'
    , description    text NULL
    , CONSTRAINT pk_devops_guide_guid PRIMARY KEY (guid)
    ) WITH (
      OIDS=FALSE
    );

-- START trg_devops_guide_set_all_seq
CREATE OR REPLACE FUNCTION fnc_devops_guide_set_all_seq()
    RETURNS TRIGGER
    AS $$
       BEGIN
         UPDATE devops_guide SET seq=col_serial FROM
         (SELECT guid, row_number() OVER ( ORDER BY seq) AS col_serial FROM devops_guide ORDER BY seq) AS tmp_devops_guide
         WHERE devops_guide.guid=tmp_devops_guide.guid;

         RETURN NEW;
       END;
    $$ LANGUAGE plpgsql;

 CREATE TRIGGER trg_devops_guide_set_all_seq
  AFTER UPDATE OR DELETE ON devops_guide
  FOR EACH ROW
  WHEN (pg_trigger_depth() < 1)
  EXECUTE PROCEDURE fnc_devops_guide_set_all_seq();

-1

Jeśli używasz pgAdmin3, rozwiń „Sekwencje”, kliknij prawym przyciskiem myszy sekwencję, przejdź do „Właściwości”, a na karcie „Definicja” zmień „Wartość bieżąca” na dowolną wartość. Nie ma potrzeby zadawania pytań.


3
Twoja odpowiedź nie ma wartości, jeśli przynajmniej nie powiesz nam, jakiego narzędzia używasz.
11101101b

3
To najłatwiejszy sposób, oczywiście myślę, że mówi pg admin 3
MvcCmsJon
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.