Поиск «потерянных» значений в PostgreSQL

При использовании последовательностей (sequence) в качестве первичного ключа (primary key) надо быть очень внимательным. Дело в том, что при назначении некоторые элементы последовательности случайно пропускаются, и в результате работы с таблицей базы данных некоторые записи удаляются. В принципе, эти значения можно использовать снова, однако найти их будет сложно, особенно в больших таблицах. Что же, давайте поищем.

Существуют два варианта поиска, рассмотрим каждый из них.

Первый вариант

Чтобы найти начало нашего интервала с «потерянным» значением, выполним следующий запрос:

SELECT customer_id + 1
FROM customers mo
WHERE NOT EXISTS
(
SELECT NULL
FROM customers mi
WHERE mi.customer_id = mo.customer_id + 1
)
ORDER BY customer_id;

В итоге мы получим следующие значения: 5, 9 и 11.

Однако бывает, что надо найти не только первое вхождение, но и все пропущенные. Тут подойдёт другой запрос, правда, стоит учитывать, что он является ресурсоёмким:

WITH seq_max AS (
SELECT max(customer_id) FROM customers
),
seq_min AS (
SELECT min(customer_id) FROM customers
)
SELECT * FROM generate_series((SELECT min FROM seq_min),(SELECT max FROM seq_max))
EXCEPT
SELECT customer_id FROM customers;

В итоге мы получим 5, 9 и 6.

Второй вариант

В этом случае мы получаем имя последовательности, которая связана с customer_id:

SELECT pg_get_serial_sequence('customers', 'customer_id');

И, таким образом, находим все пропущенные идентификаторы:

WITH sequence_info AS (
SELECT start_value, last_value FROM "SchemaName"."SequenceName"
)
SELECT generate_series ((sequence_info.start_value), (sequence_info.last_value))
FROM sequence_info
EXCEPT
SELECT customer_id FROM customers;

Вот и всё, возможно, вам также будут интересны следующие статьи: — «Работа с конфигурацией в PostgreSQL»; — «Безопасное изменение типа поля в PostgreSQL»; — «Полезные команды в PostgreSQL».

По материалам «15 Advanced PostgreSQL Commands with Examples» и «10 Most Useful PostgreSQL Commands with Examples».