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

Иногда нужно проверять существование таблицы в базе данных PostgreSQL. В случае её наличия мы делаем одни действия, а в случае её отсутствия — другие. Приложение может упасть в ОШИБКУ, если мы будем пытаться обращаться к данным таблицы, которой нет. Мы же не хотим, чтобы всё у нас отвалилось. Поэтому нам нужны дополнительные проверки и подстраховки на предмет существования таблиц в базе данных.

Однажды может случиться так, что при переезде табличных пространств на новые носители, часть таблиц «уедет» по другому адресу, а приложение(не СУБД) и знать не будет, что их нет. Но от СУБД подобные вещи не скроешь.

Способ № 1 — Через имена таблиц и их схем из pg_tables

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

Столбцы из таблицы pg_tables в PostgreSQL дают имена таблиц и их схем

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

SELECT * FROM pg_tables;

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

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

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

SELECT EXISTS (SELECT * FROM pg_tables WHERE tablename = 't_d' AND schemaname = 'a');

Результат выполнения в pgAdmin4

Проверили существование таблицы в базе данных PostgreSQL по имени таблицы и по имени схемы данных

Данная команда вернула TRUE. Значит такая таблица есть в текущей базе данных, а значит можно использовать это в условиях IF в ФУНКЦИЯХ.

Если мы попробуем ввести несуществующую таблицу:

SELECT EXISTS (SELECT * FROM pg_tables WHERE tablename = 't_d_bla_bla_bla' AND schemaname = 'bbbb');

то получим в ответ FALSE

Проверили отсутствие таблицы в базе данных PostgreSQL по имени таблицы и по имени схемы данных

Минусы этого способа в том, что если в в какой-то схеме вашей базы данных лежит 1 миллион таблиц, то вы будете очень долго искать её имя в таблице pg_tables, чтобы понять существует она или нет.

Способ № 2 — Можно напрямую пытаться обратиться к таблице и перехватывать ошибку, если такой таблицы нет

В этом варианте мы точно не знаем существует таблица или нет. Вместо этого мы делаем прямое обращение к нужной нам таблицы из схемы. Если нам повезёт и таблица существует, то мы получим заветное TRUE. А если таблицы нет в БД, то мы упадём в ошибку, которую и перехватим. Сам объект ошибки нам не интересен, мы его используем просто как маркер, что такой таблицы нет и получим FALSE.

Способ № 3 — Через имена типов данных из pg_type

На стороне СУБД PostgreSQL есть дополнительный инструмент, для отлова подобных ситуаций:

системный каталог pg_type

По своей сути это таблица, которая хранит все возможные типы данных из БД.

Сейчас ты наверное задаёшься вопросом: «Причём тут типы данных и названия таблиц?«. Открою для тебя один секрет. Под капотом PostgreSQL создаёт ДЛЯ КАЖДОЙ ТАБЛИЦЫ свой собственный ТИП ДАННЫХ и хранит их все с системном каталоге pg_type вперемешку со стандартными. Это нужно для того, чтобы иметь возможность корректно работать с СОСТАВНЫМИ ТИПАМИ ДАННЫХ и переливать их в МАССИВЫ или использовать в ТАБЛИЧНЫХ ФУНКЦИЯХ без проблемного типа record, который не даёт возможность обращаться к атрибутам СОСТАВНОГО ТИПА, чтобы выдёргивать нужные значения.

Как получить все типы данных из текущей базы данных?

SELECT * FROM pg_type;

Внимание! Таблица может оказаться очень большой. В неё также залетают TOAST.

Можно заранее сократить таблицу для поиска нужного нам имени таблицы:

SELECT EXISTS (SELECT typname FROM pg_type WHERE typname = 't_d'); -- true
SELECT EXISTS (SELECT typname FROM pg_type WHERE typname = 't_d_1'); -- false

Скриншот