Как сбросить последовательность первичного ключа в Postgres, когда она потеряла синхронизацию?

Этот код сбросит все последовательности в схеме public, не делая предположений о названиях таблиц или столбцов. Код протестирован на версии 8.4.

Вот функция для сброса последовательности:

CREATE OR REPLACE FUNCTION "reset_sequence" (tablename text, columnname text, sequence_name text) 
    RETURNS INTEGER AS 
    
    $body$  
      DECLARE 
      retval  INTEGER;
      BEGIN 
    
      EXECUTE 'SELECT setval( ''' || sequence_name  || ''', ' || '(SELECT MAX(' || columnname || 
          ') FROM "' || tablename || '")' || '+1)' INTO retval;
      RETURN retval;
      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%';

Данный запрос извлекает названия всех столбцов, у которых задано значение по умолчанию nextval, и вызывает функцию reset_sequence для сброса соответствующих последовательностей. Все последовательности будут сброшены до максимального значения существующих записей в таблицах плюс один.

Запрос ALTER SEQUENCE sequence_name RESTART WITH (SELECT max(id) FROM table_name); действительно не работает, так как команда ALTER SEQUENCE не может принимать подзапросы. Вместо этого вы можете использовать функцию setval для установки значения последовательности на максимальное значение id из вашей таблицы.

Корректный способ это сделать:

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

Этот запрос найдет последовательность, связанную с столбцом id вашей таблицы table_name, и установит ее значение равным максимуму id, найденному в этой таблице. Убедитесь, что вы запустили этот запрос с достаточными правами, и у вас есть таблица и столбец с именами, указанными в запросе.

Этот команду можно использовать для изменения значения автоматически сгенерированной последовательности в PostgreSQL:

ALTER SEQUENCE "your_sequence_name" RESTART WITH 0;

Вместо нуля вы можете поставить любое число, с которого хотите перезапустить последовательность.

Имя последовательности по умолчанию будет иметь формат "ИмяТаблицы_ИмяПоля_seq". Например, если ваше имя таблицы — "MyTable", а поле называется "MyID", то имя последовательности будет "MyTable_MyID_seq".

Это ответ аналогичен ответу @murugesanponappan, но в его решении есть синтаксическая ошибка. Нельзя использовать подзапрос (select max()...) в команде ALTER. Поэтому вы должны использовать либо фиксированное числовое значение, либо переменную вместо подзапроса.

Для сброса всех последовательностей в PostgreSQL, невзирая на имена таблиц и столбцов, кроме предполагаемого, что первичный ключ каждой таблицы называется "id", можно использовать следующую функцию и запрос.

Сначала создаем функцию reset_sequence, которая принимает имя таблицы и имя столбца, затем устанавливает значение последовательности на максимальное значение текущих данных в данном столбце, увеличенное на 1.

Вот сам код функции:

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

Этот запрос соберет все столбцы, которые используют последовательности, и вызовет функцию reset_sequence для каждого из них. Таким образом, все последовательности соответствующих столбцов в таблицах будут сброшены к правильным значениям на основе текущих данных.

Эти функции сопряжены с рисками, когда имена последовательностей, имена столбцов, имена таблиц или схем содержат необычные символы, такие как пробелы, знаки препинания и подобное. Я написал следующую функцию:

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

Вы можете вызвать эту функцию для одной последовательности, передав её OID, и она вернёт наибольшее число, использованное в любой таблице, где последовательность указана по умолчанию. Или вы можете запустить её с таким запросом, чтобы сбросить все последовательности в вашей базе данных:

SELECT relname, setval(oid, sequence_max_value(oid))
   FROM pg_class
  WHERE relkind = 'S';

Используя другой квалификатор, вы можете сбросить только последовательности в определённой схеме и так далее. Например, если вы хотите настроить последовательности в схеме "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';

Обратите внимание, что из-за того, как работает setval(), вам не нужно добавлять 1 к результату.

В заключение, хочу предупредить, что некоторые базы данных могут иметь значения по умолчанию, связывающие их с последовательностями таким образом, что системные каталоги не содержат полной информации о них. Это происходит, когда вы видите что-то подобное в psql с помощью команды \d:

alvherre=# \d baz
                     Таблица «public.baz»
 Колонка |  Тип   |                 Модификаторы                  
---------+---------+------------------------------------------------
 a       | integer | default nextval(('foo_a_seq'::text)::regclass)

Обратите внимание, что вызов nextval() в этом значении по умолчанию имеет приведение к ::text помимо приведения к ::regclass. Я думаю, что это связано с тем, что базы данных были сгенерированы с помощью pg_dump из старых версий PostgreSQL. В результате функция sequence_max_value() будет игнорировать такую таблицу. Чтобы исправить проблему, вы можете заново определить значение по умолчанию, чтобы оно ссылается на последовательность напрямую без приведения:

alvherre=# ALTER TABLE baz ALTER a SET DEFAULT nextval('foo_a_seq');
ALTER TABLE

После этого psql отобразит это правильно:

alvherre=# \d baz
                     Таблица «public.baz»
 Колонка |  Тип   |             Модификаторы              
---------+---------+----------------------------------------
 a       | integer | default nextval('foo_a_seq'::regclass)

Как только вы это исправите, функция будет работать корректно для этой таблицы, а также для всех других, которые могут использовать ту же последовательность.