Создание таблицы в PostgreSQL только если она не существует

Быстрый ответ

Для создания таблицы в PostgreSQL, при условии, что она ещё не существует, используется следующий запрос:

CREATE TABLE IF NOT EXISTS my_table (
    id SERIAL PRIMARY KEY,
    info TEXT
);

Фраза IF NOT EXISTS осуществляет проверку нахождения таблицы и помогает избежать возникновения избыточных ошибок.

Учет регистра в PostgreSQL

В PostgreSQL идентификаторы обладают свойством чувствительности к регистру. Рекомендуется применять имена таблиц в нижнем регистре, как показано ниже:

-- Использование имен таблиц в нижнем регистре снижает вероятность возникновения проблем
CREATE TABLE IF NOT EXISTS my_table (
    id SERIAL PRIMARY KEY,
    info TEXT
);

Такой подход соответствует стандартам наименований и гарантирует совместимость с содержимым каталога pg_catalog.pg_tables.

Применение SECURITY DEFINER

Для ограничения прав пользователей на создание таблиц может быть полезно использовать функции с ключевым словом SECURITY DEFINER:

CREATE OR REPLACE FUNCTION create_my_table()
RETURNS void LANGUAGE plpgsql SECURITY DEFINER AS $$
BEGIN
    -- Проверяем, есть ли таблица 'my_table' в схеме 'public'?
    IF NOT EXISTS (SELECT FROM pg_catalog.pg_tables 
                   WHERE schemaname = 'public' AND tablename = 'my_table') THEN
        EXECUTE 'CREATE TABLE public.my_table (
            id SERIAL PRIMARY KEY,
            info TEXT
        )';
    END IF;
END;
$$;

-- Выполняем функцию и следим за результатом
SELECT create_my_table();

Выполнение функции осуществляется с правами её создателя, что обеспечивает дополнительный уровень безопасности.

Проверка привилегий роли

Перед созданием таблицы необходимо убедиться в наличии соответствующих привилегий у роли:

-- Производим проверку прав роли на создание таблиц в схеме 'public'
SELECT has_schema_privilege('current_user', 'public', 'CREATE');

Успешное выполнение запроса гарантирует отсутствие ошибок авторизации.

Визуализация

Процесс создания таблицы может быть более понятным, если представить его через следующую схему:

Этап:           Существование таблицы   | Действие:
----------------------------------------|-------------------------
Отсутствует     | Устанавливаем таблицу (🏗️ CREATE TABLE)
Присутствует    | Обходим дублирование (🚫 Без действий)

В свою очередь, обновление таблицы происходит с помощью CREATE TABLE IF NOT EXISTS:

CREATE TABLE IF NOT EXISTS new_table (
    id SERIAL PRIMARY KEY,
    data TEXT
);

Таким образом, получаем надёжное решение без излишних действий:

🏢 Существующая таблица (👍 Таблица уже создана и не требует изменений)
ИЛИ
🏗️ Новая таблица (🆕 Таблица успешно создана)

Многократное использование функции CREATE OR REPLACE FUNCTION

Целесообразно инкапсулировать процесс создания таблицы в PL/pgSQL функцию, чтобы избежать его повторения:

CREATE OR REPLACE FUNCTION create_unique_table(tablename TEXT)
RETURNS void LANGUAGE plpgsql AS $$
BEGIN
    -- Проверяем, не была ли таблица уже создана
    IF NOT EXISTS (SELECT FROM pg_tables WHERE tablename = lower($1)) THEN
        EXECUTE format('CREATE TABLE %I (id SERIAL PRIMARY KEY)', $1);
    END IF;
END;
$$;

-- Проводим тестирование функции!
SELECT create_unique_table('unique_table');

Данная функция проверяет существование таблицы перед её созданием, а %I защищает от SQL-инъекций.

Назначение значений по умолчанию и установка ограничений

Правильное создание таблицы также подразумевает настройку первичных ключей и значений по умолчанию:

-- Комплексное создание таблицы
CREATE TABLE IF NOT EXISTS my_table (
    id SERIAL PRIMARY KEY,
    info TEXT DEFAULT 'default info'
);

Применяя этот подход, мы повышаем целостность данных и их смысловую нагрузку.

Завершение работы и очистка

После создания таблицы цельно убрать лишние функции:

-- Проводим удаление функции из базы данных
DROP FUNCTION IF EXISTS create_my_table();

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

Двойная проверка через information_schema

Для дополнительной верификации существования таблицы можно использовать information_schema.tables:

-- Дополнительная проверка на существование таблицы
SELECT 
    * 
FROM 
    information_schema.tables 
WHERE 
    table_schema = 'public' 
    AND table_name = 'my_table';

Отсутствие записей в результате указывает на отсутствие таблицы. Если запись присутствует, таблица существует.

Приоритет осторожности при изменениях в базе данных

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