SQL: устанавливаем генератор последовательности SEQUENCE на заданное значение

Одной из частых проблем при работе с базами данными, к примеру PostgreSQL, являются "шальные ручки", с помощью которых пользователи добавляют/изменяют значения в полях завязанных на определенной последовательности (к примеру в поле основного/уникального ключа), в обход этой самой последовательности. Такие действия, при очередной корректной вставке, использующей генератор этой последовательности, приводят к популярной ошибке:


[ERROR] An exception occurred while executing a query: SQLSTATE[23505]: Unique violation: 7
ERROR:  duplicate key value violates unique constraint "column_pkey"
DETAIL:  Key (column_id)=(3) already exists.

Иными словами, суть проблемы в том, что текущее значение последовательности входит в коллизию со значением, которое уже присутствует в изменяемой таблице. Или скажем более предметно, на примере ошибки выше: мы взяли из генератора последовательности следущее значение равное трём ((column_id)=(3)) и пытаемся вставить его в поле column_id, но такое значение уже присутствует и срабатывает ограничение на уникальность (unique constraint).

Решение данной проблемы очевидно: во первых, сделать а-та-та владельцу "шальных ручек", во вторых, актуализировать (перемотать) генератор последовательности на следующее корректное значение.

Прежде чем что-то перематывать, необходимо выяснить что. Для этого, можно посмотреть на исходный SQL запрос, где может участвовать генератор последовательности, либо ознакомиться с DDL таблицы в которую вносятся изменения и в котором может быть описана искомая последовательность, либо, что более неочевидно, найти место, где извлекается значение из генератора завязанного на данную таблицу (к примеру в коде приложения вызвавшего исходный SQL запрос).

Ещё один вариант, если ваше поле имеет тип SERIAL, можно узнать его генератор с помощью запроса (здесь и далее все SQL запросы будут даны на примере их использования с СУБД PostgreSQL):


SELECT pg_get_serial_sequence('scheme.table', 'column_name')

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


SELECT *
FROM information_schema.sequences
ORDER BY sequence_name

Конечно, мы можем отфильтровать их для желаемой схемы БД и даже подглядеть последнее значение последовательности (здесь <sequence_name> имя вашего генератора последовательности):


SELECT last_value FROM <sequence_name>

На этом этапе, надеюсь, последовательность, значение которой необходимо исправить, найдена, приступаем к вариантам ручного изменения её значения.

Вариант 1: через использование функции setval


SELECT setval('<sequence_name>', <sequence_value>)

где <sequence_value> желаемое целочисленное значение последовательности. По умолчанию, следующий вызов nextval на генераторе выдаст установленное sequence_value + 1. Если вы желаете, чтобы следующий вызов nextval вернул именно введенное sequence_value, передайте в setval третий параметр как false, к примеру:


SELECT setval('user.user_id_seq', 77, false)

после вызова SELECT nextval('user.user_id_seq') вернёт именно 77

Вариант 2: через DDL запрос


ALTER SEQUENCE user.user_id_seq RESTART WITH 77;

Который аналогичен нашему последнему запросу через setval с опцией false, т.е. следующий nextval на данном генераторе вернёт 77.

Ссылки на документацию: