Поиск «потерянных» значений в PostgreSQL | OTUS
🔥 Начинаем BLACK FRIDAY!
Максимальная скидка -25% на всё. Успейте начать обучение по самой выгодной цене.
Выбрать курс

Курсы

Программирование
iOS Developer. Basic
-25%
Python Developer. Professional
-25%
Разработчик на Spring Framework
-25%
Golang Developer. Professional
-25%
Python Developer. Basic
-25%
iOS Developer. Professional
-25%
Node.js Developer
-25%
Unity Game Developer. Professional
-25%
React.js Developer
-25%
Android Developer. Professional
-25%
Software Architect
-25%
C++ Developer. Professional
-25%
Backend-разработчик на PHP Web-разработчик на Python Алгоритмы и структуры данных Framework Laravel PostgreSQL Team Lead Разработчик голосовых ассистентов и чат-ботов Архитектура и шаблоны проектирования Agile Project Manager Нереляционные базы данных Супер - интенсив по паттернам проектирования Супер-практикум по использованию и настройке GIT IoT-разработчик Подготовка к сертификации Oracle Java Programmer (OCAJP) Супер-интенсив «СУБД в высоконагруженных системах» Супер-интенсив "Azure для разработчиков"
Инфраструктура
Мониторинг и логирование: Zabbix, Prometheus, ELK
-25%
DevOps практики и инструменты
-25%
Архитектор сетей
-25%
Инфраструктурная платформа на основе Kubernetes
-25%
Супер-интенсив «ELK»
-16%
Супер-интенсив «IaC Ansible»
-16%
Administrator Linux. Professional MS SQL Server Developer Безопасность Linux PostgreSQL Reverse-Engineering. Professional CI/CD VOIP инженер Супер-практикум по работе с протоколом BGP Супер - интенсив по паттернам проектирования Супер - интенсив по Kubernetes Administrator Linux.Basic Супер-интенсив "Tarantool"
Специализации Курсы в разработке Подготовительные курсы
+7 499 938-92-02

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

Postgre_Deep_29.04-5020-b35724.png

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

postgre_06_1-20219-201ff6.png

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

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

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

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

Не пропустите новые полезные статьи!

Спасибо за подписку!

Мы отправили вам письмо для подтверждения вашего email.
С уважением, OTUS!

Автор
0 комментариев
Для комментирования необходимо авторизоваться
🎁 Максимальная скидка!
Черная пятница уже в OTUS! Скидка -25% на всё!