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.
Ссылки на документацию: