PostgreSQL | Как проверить таблицу на пустоту?

Простая ситуация — таблица точно существует

Допустим мы 100% уверены, что таблица к которой мы обращаемся существует. Нам не нужно переживать, что при обращении к ней вывалится ошибка.

Проще всего проверить существующую таблицу на пустоту — это использовать функцию count() для подсчёта количества записей в таблице.

Если записи есть в таблице, то нам вернётся что-то больше нуля.

Если записей нет в таблице, то мы гарантированно получим ноль.

Пример:

SELECT count(*) FROM b.t_d_77;

SELECT count(*) FROM b.t_d_200000;

count - 332 записи в таблице в PostgreSQL — count — 332 записи в таблице в PostgreSQL

count - 0 записей в таблице в PostgreSQL — count — 0 записей в таблице в PostgreSQL

Стало быть, если мы хотим получить логическое значение «ПУСТОЙ ТАБЛИЦЫ», тогда

SELECT (SELECT count(*) FROM b.t_d_77) = 0;

SELECT (SELECT count(*) FROM b.t_d_200000) = 0;

Таблица не пустая - false в PostgreSQL — Таблица не пустая — false в PostgreSQL

Таблица пустая - true в PostgreSQL — Таблица пустая — true в PostgreSQL

Сложная ситуация — таблица возможно существует

Если мы не знаем, есть таблица в базе данных или её нет, тогда сперва нужно как-то проверить это.

Представление pg_tables даёт доступ к полезной информации обо всех таблицах в базе данных.

Команда для вызова представления со списком названий схем и таблиц:

SELECT * FROM pg_tables;

Данная команда выведет все возможные имена таблиц, схем и их владельцев в ТЕКУЩЕЙ базе данных.

Фрагмент из таблицы pg_tables в PostgreSQL с именами схем и таблиц

С этого момента мы точно можем проверить существование таблицы в базе данных если будем использовать SELECT с подзапросом:

SELECT EXISTS (SELECT * FROM pg_tables WHERE tablename = 't_d_77' AND schemaname = 'b');
SELECT EXISTS (SELECT * FROM pg_tables WHERE tablename = 't_d_200000' AND schemaname = 'b');
SELECT EXISTS (SELECT * FROM pg_tables WHERE tablename = 't_d_10000000000' AND schemaname = 'b');

Таблица t_d_77 присутствует в представлении pg_tables в PostgreSQL

Таблица t_d_200000 присутствует в представлении pg_tables в PostgreSQL

Таблица t_d_10000000000 отсутствует в представлении pg_tables в PostgreSQL

Мы используем значения ячеек по столбцам tablename и schemaname, чтобы точно ограничить список искомых таблиц по всей базе данных.

В нашем случае все подобные таблицы лежат в схеме ‘b‘, и других полезных объектов там больше нет.

Присутствие таблицы выдаёт нам заветное true, а если таблицы нет в представлении pg_tables, тогда мы просто получим false.

Существование мы проверили и теперь можем проверять на пустоту.

Чтобы не писать отдельную функцию, можно воспользоваться оператором DO. Внутри будем использовать КУРСОР, для простоты написания цикла.

Будем удалять пустые таблицы пачками до 5000 штук. Каждый раз нужно будет перепроверять содержимое pg_tables, так как оно будет меняться (уменьшаться).

— Удаление пустых таблиц из схемы ‘b’

— Ограничение в 5000 штук, иначе переполнится память от блокировок

DO $$

DECLARE

cur refcursor; — объявляем курсор

tname text; — будет хранить имена таблиц из курсора

count_of_empty_table bigint := 0; — будетнакапливать количество пустых таблиц

boolvar boolean; — будет хранить ПУСТАЯ ТАБЛИЦА или нет

BEGIN

— Открываем курсор для выборки живых таблиц в схеме b

OPEN cur FOR SELECT tablename — выбираем только значение tablename

FROM pg_tables — из pg_tables на каждой итерации

WHERE tablename LIKE ‘t_d_%’ — сопоставляем строку с шаблоном

AND schemaname = ‘b’ — указываем схему, внутри которой лежат таблицы

ORDER BY tablename ASC — обязательно сортируем результаты

LIMIT 5000 — для теста ограничиваем цикл курсора

OFFSET 0 * 5000; — для удобства указываем сдвиги

— Запускаем цикл по курсору

— Границы цикла выбираются курсором самостоятельно

LOOP

FETCH cur INTO tname; — достаём значение из курсора и складываем его в tname

EXIT WHEN NOT FOUND; — выходим из цикла, если записей больше нет

<<step_cycle>>

BEGIN

— Действие, которое нужно выполнить в цикле курсора —

— Присваиваем логическое значение пустой таблицы в boolvar

EXECUTE ‘SELECT (SELECT count(*) FROM b.’|| tname ||‘) = 0;’ INTO boolvar;

IF boolvar

THEN

EXECUTE ‘DROP TABLE b.’|| tname ||‘;’;

— RAISE NOTICE ‘Таблица b.% пустая‘, tname;

count_of_empty_table := count_of_empty_table + 1;

ELSE

—RAISE NOTICE ‘Таблица b.% имеет записи‘, tname;

END IF;

END step_cycle;

END LOOP;

— Информационное сообщение по завершению

RAISE NOTICE ‘Удалено пустых таблиц: % штук‘, count_of_empty_table;

END;

$$;

Информационные ссылки

Официальный сайт WEB-оболочки pgAdmin — https://www.pgadmin.org

Официальный сайт СУБД PostgreSQL — https://www.postgresql.org

Раздел «Глава 52. Системные каталоги» — https://postgrespro.ru/docs/postgresql/14/catalogs

Раздел «52.92. pg_tables» — https://postgrespro.ru/docs/postgresql/14/view-pg-tables

Раздел «52.62. pg_type» — https://postgrespro.ru/docs/postgresql/14/catalog-pg-type