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

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?

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.

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

Zresetuj sekwencję:

SELECT setval('sequence_name', 0);

Aktualizacja aktualnych rekordów:

UPDATE foo SET id = DEFAULT;

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;

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

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

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;

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

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;

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

ALTER SEQUENCE table_tabl_id_seq RESTART WITH 6;

Gdzie moja tabela nazywa się table

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)

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

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ń.